Adding too measures together

[Anonymous]

Hello

I'm creating a chart showing actual expenditure to date and future forecast.

I've created two separate measures for:

Cumulative payments to date 

Cumulative payments = var contextDate = MAX('Calendar'[Date]) var output = CALCULATE( SUM('Money Transfers 201819'[MT amount for charts]), FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] <= contextDate) ) RETURN IF( contextDate <= Today(), output )

Cumulative future forecasts

Cumulative forecasts in future = CALCULATE(SUM('Forecast Report Oct 2016'[Forecast amount]),FILTER(ALL('Calendar'[Date]),AND('Calendar'[Date]>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),'Calendar'[Date]<=MAX('Calendar'[Date]))))

When i add both measures to a chart there are two separate lines. 

What i would like to do is create a third measure that basically sums these two together. Eg, at the moment, if there are no December forecasts, the cumulative forecast line starts at 0. I would like the latest month's cumulative payments to be added to the future forecasts so the line basically 'keeps going up' rather than dropping down for zero forecasts.

I hope this makes sense, i'm only a beginner user. If anyone could help me with a measure to 'add' these separate measures together than would be amazing!

Thanks!

[Greg_Deckler]

I believe that should just be:

Measure 3 = 
VAR __month = MONTH(MAX('Calendar'[Date]))
RETURN
CALCULATE(ALL([Cumulative payments to date]),MONTH('Calendar'[Date])=__month -1 + [Cumulative future forecasts])

Something like that. 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

Also See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Hi Greg

Thanks for your reply.

I've tried using the formula but I get the following error:

'A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'

Any ideas what this might means?

Thanks!

[Greg_Deckler]

That means I seriously screwed up my syntax. I think this is what I was going for:

Measure 3 = 
VAR __month = MONTH(MAX('Calendar'[Date]))
RETURN
CALCULATE([Cumulative payments to date],FILTER(ALL(Calendar),MONTH('Calendar'[Date])=__month -1) + [Cumulative future forecasts])

Been a long day.

[Anonymous]

Thanks Greg, now i get this error:

A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Any ideas?

Thanks for your help so far!

[Greg_Deckler]

Shoot, paren in the wrong spot! Syntax...

Measure 3 = 
VAR __month = MONTH(MAX('Calendar'[Date]))
RETURN
CALCULATE([Cumulative payments to date],FILTER(ALL(Calendar),MONTH('Calendar'[Date])=__month -1)) + [Cumulative future forecasts]

This is what happens when I don't actually test my DAX code in Power BI and try to wing it.

[Anonymous]

Thanks again for your reply.

Unfortunately that measure basically re-creates a line the same as the existing measure for Cumulative Forecasts.

I was hoping to get a line that starts from July thats show cumulative payments to date. For example, as at 31 December, the total cumulative figure for the first 6 months would be $1,000,000. The line would then change to Cumulative Forecasts where the January figure would be the $1,000,000 paid to date plus January forecasts. This would then continue on until 30 June.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...