Forum Discussion
Cumulative Surplus/Deficit Monthly
Hi all,
I hope you are well.
I am struggling with a measure now to get the right cumulative surplus/deficit each month.
I have correct measures for Incomes & Expenses respectively.
The calculation for surplus/deficit is Incomes - Expenses, thus I have a new measure created below:
4 Replies
Hi BryanLy ,
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)Looks like you need a running total. Please try an expression like this, replacing with your actual measure/table/column names. This assumes you have a Date table. If not, you'll need to remove the filter from your month and date column used in your visual with ALL(Table[Date], Table[Month]), but a Date table is recommended.
Surplus =
VAR maxdate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
[Income] - [Expenses],
ALL ( 'Date'[Date] ),
'Date'[Date] <= maxdate
)If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- Here's all the variations of running totals that you want to know as explained by the masters of their trade:
https://www.sqlbi.com/articles/computing-running-totals-in-dax/ BryanLy , You need to have date and then try
MTD Cumm= CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Cumm= CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))diff = [MTD Cumm]-[last MTD Cumm]
Date from month
date = "01-" & [Month] & "-" & [Year]
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
