Forum Discussion
Helper IIIgnore Visual Filter but Filter on One Column - Dates
So I am using the waterfall chart.
I have data for all of 2021 and data through February 2022. I want to be able to show a waterfall based on YTD Values. In order to do this, I came up with this measure:
YTD Costs =
VAR MaxMonth = CALCULATE(MAX('Calendar'[Month Num]),'Calendar'[Year Num]=MAX(('Calendar'[Year Num])))
RETURN
CALCULATE(SUM('CPU'[Value]),'Calendar'[Month Num]<=2)
I'm using MaxMonth Variable as I have a date range slider and want to essentially take the maximum month in the maximum year. In this example I have the slider selecting my whole date range (2021-Feb 2022).
The problem with the above measure is that I want to use the variable MaxMonth, but when I do, it removes some filter and I'm comparing all of 2021 vs my 2022 data which only has data through February. If I hard code the number 2 in the measure as shown above, it produces the results I want. However, I want the measure to be dynamic.
Hi PowerUser123 ，
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can update your measure as below:
YTD Costs = VAR _maxyear = CALCULATE ( MAX ( 'Calendar'[Year Num] ), ALLSELECTED ( 'Calendar' ) ) VAR MaxMonth = CALCULATE ( MAX ( 'Calendar'[Month Num] ), FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Year Num] = _maxyear ) ) RETURN CALCULATE ( SUM ( 'CPU'[Value] ), 'Calendar'[Month Num] <= MaxMonth )If the above one can't help you get the desired result, please provide some sample data in CPU table and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
How to upload PBI in Community
Best Regards
3 Replies
PowerUser123Any ideas on this?
- PowerUser123
Is there a filter context being applied that I'm missing?
Hi PowerUser123 ，
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can update your measure as below:
YTD Costs = VAR _maxyear = CALCULATE ( MAX ( 'Calendar'[Year Num] ), ALLSELECTED ( 'Calendar' ) ) VAR MaxMonth = CALCULATE ( MAX ( 'Calendar'[Month Num] ), FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Year Num] = _maxyear ) ) RETURN CALCULATE ( SUM ( 'CPU'[Value] ), 'Calendar'[Month Num] <= MaxMonth )If the above one can't help you get the desired result, please provide some sample data in CPU table and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
How to upload PBI in Community
Best Regards