Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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...
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!
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.
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!
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.
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.
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...
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |