Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I use the below DAX for a YTD cumulative and it works fine. Dates are well linked and when i drill-down to week or select it works fine.
Solved! Go to Solution.
YTD = calculate(sum(Orders[Orders]),filter(all('Orders'),'Orders'[calendarWeek]<=max('Orders'[calendarWeek])))
Check if this can work
Last Month YTD = var _max =maxx(ALLSELECTED('Date'),'Date'[Month Year])
return
CALCULATE([YTD],FILTER('Date','Date'[Month Year]=_max))
Hi @vaspiliop ,
You may create column and measure like DAX below, and put [Max Month YTD] into Values box of chart visual.
Column:
YearMonth = VALUE(FORMAT('Orders'[Date],"yyyymm"))
Measure:
Max Month YTD =
VAR _MaxYearMonth =
CALCULATE ( MAX ( 'Orders'[YearMonth] ), ALLSELECTED ( 'Orders' ) )
RETURN
IF (
MAX ( 'Orders'[YearMonth] ) = _MaxYearMonth,
CALCULATE ( [YTD], FILTER ( 'Orders', 'Orders'[YearMonth] = _MaxYearMonth ) ),
BLANK ()
)
>>to keep some filters working? since the ALL function clears filters also from other fields where i would need them to still filter.
In addition , for this question, you could change the ALL function to ALLSELECTED function.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
YTD = calculate(sum(Orders[Orders]),filter(all('Orders'),'Orders'[calendarWeek]<=max('Orders'[calendarWeek])))
Check if this can work
Last Month YTD = var _max =maxx(ALLSELECTED('Date'),'Date'[Month Year])
return
CALCULATE([YTD],FILTER('Date','Date'[Month Year]=_max))
Many thanks @amitchandak , appreciate your help! this actually solved it - just one more question please
is there a way for this formula:
YTD = calculate(sum(Orders[Orders]),filter(all('Orders'),'Orders'[calendarWeek]<=max('Orders'[calendarWeek])))
to keep some filters working? since the ALL function clears filters also from other fields where i would need them to still filter.
thanks
User | Count |
---|---|
94 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |