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

 

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

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

I do use the date dimension for the calculations but when I posted the example I was troubleshooting and removed all other tables to try to simplify as much as possible. I wanted to be sure the issue wasn't coming from an incorrect join. I also simplified the calculation as well. I am not really just getting an opening balance. I am doing calculations with several opening and closing balances. 

 

When troubleshooting I like to get down to the simplest possible situation where I get the error to pinpoint exactly what is going wrong.

 

Everything eventually worked well. I deployed the model to SSAS, created my Power BI report, and published it to a web part in SharePoint. It refreshes from a SQL Server database nightly.

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

    JTwohig

     

    Excellent! It is clear now. Please mark this post as solved.

     

    N -