Forum Discussion

JTwohig's avatar
JTwohig
Icon for Helper I rankHelper I
9 years ago
Solved

DAX Closing and Opening Balances

I have a table that contains a daily snapshot of an amount. I can get the amount at the end of each month with this formula:   Closing Balance:=CALCULATE(Sum(FactTable[AmountToDate]),LASTDATE(FactT...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    JTwohig

     

    Hi, if you always finished in the end of the month. this can help you

     

    ClosingBalance-1month-Alt =
VAR EndofPrevMonth =
    PREVIOUSMONTH ( Table1[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER ( ALL ( Table1 ), Table1[Date] = EndofPrevMonth )
    )

     

    Also you can review this DAX Functions:

     

    OPENINGBALANCEMONTH

    CLOSINGBALANCEMONTH

     

    Regards

     

    Victor

    Lima - Peru

  • fhill's avatar
    fhill
    9 years ago

    FINALLY!!!!    I got it....  This link helped!

    https://community.powerbi.com/t5/Desktop/Help-using-Earlier-in-New-Measure/td-p/55799

     

    EndofPriorMonth = CALCULATE(SUM(Table1[Balance]), FILTER(ALL(Table1), SUMX( FILTER( Table1, EARLIER(Table1[Date]) = LASTDATE(PREVIOUSMONTH(Table1[Date])) ), Table1[Balance])))

       ** What this does.. .Sum Blance,

                     Look at ALL Rows, (Filter ALL)

                     SUMX (Sums for each row of....)

                     Filter again (not sure why)

                     Compare 'previous row' (EARLIER) with Last Date of Pervious Month

                     When found, return Balance.

    EndOfMonth = CALCULATE(SUM(Table1[Balance]), ENDOFMONTH(Table1[Date]))

    Change = [EndOfMonth] - [EndofPriorMonth]

     

     

     

JTwohig's avatar
JTwohig
Icon for Helper I rankHelper I
9 years ago

I thought I would post this in case someone else is trying to do the same thing I was. 

 

There were two solutions suggested here that both worked when I looked at the whole table. However, when I tried to filter the results by customer or some other dimension the last month closing balance remained the last month balance for everything. It was not filtered.

 

What worked was to remove only the filter from the date rather than the whole table. If I understand the terminology that means it keeps the context for everything else but not for the dates.

 

instead of using FILTER(All(FactTable),

I used Filter(All(FactTable[CalendarYear],FactTable[MonthName],FactTable[Date]),

 

That way I got the correct balances whether I was looking at the whole table or only looking at certain dimensions.

  • nickchobotar's avatar
    nickchobotar
    Icon for Skilled Sharer rankSkilled Sharer
    9 years ago

    JTwohig

     

    Before this post is closed. What happens if you use FILTER(ALLSELECTED(FactTable)) instead of

    Filter(All(FactTable[CalendarYear],FactTable[MonthName],FactTable[Date]),?

    Also, please consider adding a date dimension to your model.

     

    • JTwohig's avatar
      JTwohig
      Icon for Helper I rankHelper I
      9 years ago

      That worked as well. I like it much better because it is simpler. Thanks.

       

      I do have a date dimension, along with several others. My model uses data from two fact tables so the only way to get the correct data from each fact table is to use separate dimiensions. 

      • nickchobotar's avatar
        nickchobotar
        Icon for Skilled Sharer rankSkilled Sharer
        9 years ago

        Hi JTwohig

         

        Great! I am glad to hear the ALLSELECTED() worked for you.

         

        If I understood you correctly you are using two separate dimensions that play the same role to filter your two fact tables. If so then there is a room to optimize your model  so you can use only one Date dimension. If you are interested please post a picture of your model diagram.

         

        N-