Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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(FactTable[Date]))
I need the balance at the end of the prior month so I can subtract and get the change in the month. I tried the following formula:
Last Month Closing:=CALCULATE([Closing Balance],PREVIOUSMONTH(LASTDATE(FactTable[Date])))
This gives me the correct closing balance for the last month of the year but all other months are blank. The results show below. I have tried many different ways but always get the same thing. What am I missing?
Values | |||
CalendarYear | MonthName | Closing Balance | Last Month Closing |
2016 | March | 59,791,699.31 | |
April | 62,596,269.14 | ||
May | 51,852,607.79 | ||
June | 53,945,802.67 | ||
July | 53,729,133.16 | ||
August | 57,174,277.35 | ||
September | 56,780,543.07 | ||
October | 57,852,783.08 | ||
November | 55,973,021.78 | ||
December | 57,976,571.96 | ||
2016 Total | 57,976,571.96 | 55,973,021.78 | |
2017 | January | 55,479,645.44 | |
February | 50,214,362.52 | ||
March | 44,361,759.05 | ||
April | 46,053,405.57 | ||
May | 48,841,553.83 | ||
June | 52,345,876.41 | ||
July | 56,329,590.12 | ||
August | 55,859,204.39 | ||
2017 Total | 55,859,204.39 | 56,329,590.12 | |
Grand Total | 55,859,204.39 | 56,329,590.12 |
Solved! Go to Solution.
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
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]
Proud to give back to the community!
Thank You!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |