Search code examples
functionpowerbipowerquerym

Set last month ending balance to next month open balance: Power Query M Function

Using a Power Query M function, I am trying to set current month's ending balance to be next month's opening balance. I have tried various different date functions (i.e. Date.IsInPreviousMonth) but cannot get this to work.

The attached image shows what I am trying to accomplish using Power Query M formula.

enter image description here

Solution

  • This is possible with M but is quite complex. Here is a PowerBI Community post that gives options.

    I would prefer to use DAX's PREVIOUSMONTH function as opposed to M.

    Create a new measure along the lines of: 

    Previous Month Balance = 
             CALCULATE(SUM('Fact - Sales'[Cost Price]),
             PREVIOUSMONTH('Dimension - Calendar'[Date]))