Cumulative Surplus/Deficit Monthly

[BryanLy]

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:

Cumulative Surplus/Deficit - Actual =  [Cumulative Incomes - Actual] - [Cumulative Expenses - Actual]

However, the measure above produces the amount of each month, not a cumulative amount. I wanted to use Calculate(Sum()) but it is not a column.

Currently, the results are like below:

Jul:  -10

Aug: 20

Sep: -35

My expected/desired results should be:

Jul: -10

Aug: 10

Sep: -25

I am running out of idea and I really want to get this done. I hope someone can help me here or I hope I can think of something tomorrow when I wake up.

Thank you

B

[amitchandak]

@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-Y...


Appreciate your Kudos.

[Anonymous]

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/

[mahoneypat]

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

[harshnathani]

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)