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.
I working on doing a cumulative line which is basic using the standard appraoch.
Forecast Cumulative = CALCULATE( SUM('Financial'[Forecast]), FILTER( ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )
However i would like to be able to slice the data by a given year. But the results i'm getting is wrong.
The image shows that for Year 2019 the total should be 810k but the cumulative is 910K which is because it is pulling the value from the prior year (2018). I have tried a few different approaches but i've been hitting the proverbial wall on this one.
Solved! Go to Solution.
Found my solution after some research and testing.
Forecast Cumulative = CALCULATE( SUM('Financial'[Forecast]), FILTER( ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )
ALL was causing a filter context outside of my slicer but ALLSELECTED worked like a charm.
Found my solution after some research and testing.
Forecast Cumulative = CALCULATE( SUM('Financial'[Forecast]), FILTER( ALLSELECTED('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]) ) )
ALL was causing a filter context outside of my slicer but ALLSELECTED worked like a charm.
Hi,
Try this measure
=CALCULATE(SUM('Financial'[Forecast]),DATESYTD('Calendar'[Date],"31/12"))
Hope this helps.
HI @Aree,
According to your description and snapshot, your visual seems displayed incorrect records.
It seems like financial table not contains records before 9-2019, but calendar date and measure formula expand them with 100k to replace blank records.
Can you please share some sample data to help us clarify your scenario and testing?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi,
You may refer to my solution here.
Hope this helps.
lol thanks for the effort mate. I found a solution this morning after some testing and researching.
Special Mention to @Ashish_Mathur
Who offered alternate solution which is pretty sweet.
IF(ISBLANK([Forecasts]),BLANK(),CALCULATE([Forecasts],DATESYTD('Calendar'[Date],"30/6")))
You are welcome.
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 |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |