Cumulative cashflow actual and forecast

[lesnalder]

I'm having a problen writing the DAX to get a running total. It's OK until I bring in the Revenue Type column.  The Actual and Forecast Monthly Revenue figures come from two different tables. I've created measures and added them together to give the Monthly Revenue.

The tables are linked to a date dimension table and a Revenue Type table.

This is what I want

Financial Year	Revenue type		Monthly Revenue	Running total
2020-21	Actual	Apr	1,000	1,000
	Forecast	May	1,000	2,000
		Jun	1,000	3,000
		Jul	1,000	4,000
		Aug	1,000	5,000
		Sep	1,000	6,000
		Oct	1,000	7,000
		Nov	1,000	8,000
		Dec	1,000	9,000
		Jan	1,000	10,000
		Feb	1,000	11,000
		Mar	1,000	12,000
2021-22	Forecast	Apr	1,000	1,000
		May	1,000	2,000
		Jun	1,000	3,000
		Jul	1,000	4,000

And this is what I'm getting

			Monthly Revenue	Running total
2020-21	Actual	Apr	1,000	1,000
		May		1,000
		Jun		1,000
		Jul		1,000
		Aug		1,000
		Sep		1,000
		Oct		1,000
		Nov		1,000
		Dec		1,000
		Jan		1,000
		Feb		1,000
		Mar		1,000
	Forecast	May	1,000	2,000
		Jun	1,000	3,000
		Jul	1,000	4,000
		Aug	1,000	5,000
		Sep	1,000	6,000
		Oct	1,000	7,000
		Nov	1,000	8,000
		Dec	1,000	9,000
		Jan	1,000	10,000
		Feb	1,000	11,000
		Mar	1,000	12,000
2021-22	Forecast	Apr	1,000	1,000
		May	1,000	2,000
		Jun	1,000	3,000
		Jul	1,000	4,000

My current DAX measure is calculate([Cashflow],DATESYTD('Calendar'[Date],"31/3"))

I'd appreciate some assistance.

[stevedep]

How does the calculation for revenue look like?

[stevedep]

You can try this approach:

Sales RT :=
VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date
RETURN
CALCULATE (
[Sales Amount], -- Computes sales amount
'Date'[Date] <= MaxDate, -- Where date is before the last visible date
ALL ( Date ) -- Removes any other filters from Date
)

As described here:

https://www.sqlbi.com/articles/computing-running-totals-in-dax/

[lesnalder · Accepted solution]

Thank you for your suggestion but it didn't give me what I wanted. however in the meantime i hve used a workaround. I am nuw providing the Actual and forecast values as different series, instead of having the Revenue Type identified in the row headings and I ahve used the original cumulative measure I created

[stevedep]

Can you share your data model?