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'd like to show a graph that shows a YTD value for a measure, that also responds to a date filter. So suppose I have the following values:
Month Sales YTD
01 10 10
02 30 40
03 25 65
When I would filter my report date filter to only show the from february and onwards, the YTD value should not take the 10 into account, so the YTD value for march should be 55 instead.
I already tried using DATESBETWEEN, but that takes the filter context of the graph into account. Is there some way to use the minimum date from the date filter, and the maximum date from the graph?
Thanks
hi, @Anonymous
After my research, the formula for TOTALYTD Function is equivalent to the formula for DATESYTD Function
For example:
[YTD Sales] := TOTALYTD ( [Sales Amount], 'Date'[Date] )
[YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD ( 'Date'[Date] ) )
and DATESYTD ( 'Date'[Date] ) corresponds to a filter over the date column using FILTER called by CALCULATETABLE, such as in the following code:
CALCULATETABLE ( FILTER ( ALL ( 'Date'[Date] ), AND ( 'Date'[Date] <= MAX ( 'Date'[Date] ), YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) ) )
So you could use ALLSELECTED to modified formula as below:
Measure 2 = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALLSELECTED ( 'Date'[Date] ), AND ( 'Date'[Date] <= MAX ( 'Date'[Date] ), YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) ) ) ) )
Result:
Best Regards,
Lin
@Anonymous,
Will this work for you?
YTD = CALCULATE( SUMX( Table1, Table1[Sales] ), FILTER( ALLSELECTED(Table1), Table1[Month] <= MAX(Table1[Month]) ) )
Proud to be a Super User!
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 |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |