topic Re: YTD filter based on the selected month in DAX Commands and Tips

YTD filter based on the selected month

David83300 — Thu, 23 Jan 2025 13:43:34 GMT

Hello,

I'm trying to create a dynamic time filter based on the selected month (Always one month selected in the report):

MTD: Selected month

YTD: Janv - to selected month (No cumulation !)

FY: Janv - Dec

First I tried with calculation group but I always get cumulated YTD result. Now I try with a DAX table but I can't link the selected month in the DAX table...

Model:

DAX Filter table:

MonthFilter = Var _SelectedMonth= max(SelectedMonth[SelectedMonth]) Var _MonthFilter = UNION( ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( _SelectedMonth,_SelectedMonth,1 ) ,"Mois",INT([Value])) ,"MonthFilter","MTD" ,"Order",1) ,ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( 1,_SelectedMonth,1 ) ,"Mois",INT([Value])) ,"MonthFilter","YTD" ,"Order",2) ,ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( 1, 12 ,1 ) ,"Mois",INT([Value])) ,"MonthFilter","Full Year" ,"Order",3) ) Return _MonthFilter

Result:

Thanks for your help 🙂

Re: YTD filter based on the selected month

David83300 — Thu, 23 Jan 2025 13:54:55 GMT

Example PBIX file:

https://we.tl/t-x9zpYv40XQ

Re: YTD filter based on the selected month

Anonymous — Fri, 24 Jan 2025 06:06:49 GMT

Hi David83300 ,

I suggest you to create a Monthfilter table as below.

MonthFilter = VAR _MonthFilter = DATATABLE( "MonthFilter",STRING, "Order",INTEGER, { {"MTD",1}, {"YTD",2}, {"Full Year",3} } ) Return _MonthFilter

Then create a measure to filter your visual in visual level filter and set it to show items when value = 1.

Measure = SWITCH( MAX(MonthFilter[Order]), 1,IF(MAX(DIM_DATE[MONTH]) = SELECTEDVALUE(SelectedMonth[SelectedMonth]),1,0), 2,IF(MAX(DIM_DATE[MONTH]) <= SELECTEDVALUE(SelectedMonth[SelectedMonth]),1,0), 3,1)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: YTD filter based on the selected month

David83300 — Fri, 24 Jan 2025 12:32:23 GMT

Thanks Anonymous your solution works. But it's not very practical, I would prefer to avoid adding visual filters everywhere 😕 But maybe this is the only way ?

And by the way it's not working anymore if I switch to quarter dimension instead of month

Re: YTD filter based on the selected month

Anonymous — Mon, 27 Jan 2025 08:07:48 GMT

Hi David83300 ,

Since your requirement requires matrix visual for dynamic display, I suggest you try using measure to get dynamic results or you can try to create custom visuals by yourself.

If you want to switch to quarter dimension, we can update the measure and the MonthFilter table to achieve your goal.

Please show us a screenshot with the result you want switching to quarter dimension.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: YTD filter based on the selected month

David83300 — Wed, 29 Jan 2025 16:49:04 GMT

Hello,

Thanks Anonymous , I added a Quarter dimension in the PBIX. As you can see it's not working right now

https://we.tl/t-OeO6cwyLNw

Re: YTD filter based on the selected month

Anonymous — Mon, 03 Feb 2025 09:30:10 GMT

Hi David83300 ,

Here I update my sample, you can download and check the attachment.

If this reply still couldn't help you solve your issue, the transfer has been expired, please share again with us.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: YTD filter based on the selected month

David83300 — Mon, 03 Feb 2025 17:45:02 GMT

Hi Anonymous

Thanks but it's only working if I keep the month dimension in the chart.

I need something that can work without the month dimension, is that possible ?