Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |