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]

     

     

     

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-

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

      No, they are different dimensions. I have one date dimension plus ones for customer, project and business unit.

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

        JTwohig

         

        I see. In that case, how come you are not using your Date dimension for this time calcuation but use your Fact table ?