YTD filter based on the selected month

[David83300]

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 🙂

[David83300]

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

[Anonymous]

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.

[David83300]

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

[Anonymous]

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.

[David83300]

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 ?

[David83300]

Example PBIX file:

https://we.tl/t-x9zpYv40XQ

[Anonymous]

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.