Forum Discussion

David83300's avatar
David83300
Frequent Visitor
1 year ago

YTD filter based on the selected month

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 🙂

 

 

"PYTD"
"SELECTEDVALUE"
Filter
YTD

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

  • David83300's avatar
    David83300
    Frequent Visitor
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.