Forum Discussion
Trailing Month Slicer
Hi all,
Looking to have T3, T6 and T12 slicer tiles for my measures in BI. Have a data table which has an active relationship to my Date table. I know can add 3 columns to the date table and then add as slicer but wondering if theres a better/cleaner way so that I dont have too many columns in my date table
Since this is for a matrix and not a card, cant create measures for each one (real data is more complicated than data below)
Any help would be appreciated! Thanks in advance!
Hello MStark
hope you are doing great ,
you can do the following,
in powr query , create a period table, ( using any Ai chattool ex, CHatGPT ) . this period table would have 2 columns :
the column containning T3 , T6 and T12 and another column having the date .
so let us say your T3 corresponds to the last 3 months, T2 to the last 2 months and T12 to the last 12 months
then ask chatgpt to create for you such a table.
once created , add this table to your model .
link your period table ( created by chatgpt ) to your date table period with a many to many relationship ( using single direction from period to datetable ).
you may be wondering why many to many and 1 to many from period to datetable.
the reason is the dates for T2 , t3 and t12 are overlapping. this means, t12 contains the dates of t2 and t3, etcc.
but that would not harm your calculations in any way, since you will always be selecting only one value in your slicer, or none value, which in both cases will work.
hope that helps .
5 Replies
Eliminate the slicers and teach your users how to use the Filter Pane. It has automatic relative date filters for your scenarios.
Hello MStark
hope you are doing great ,
you can do the following,
in powr query , create a period table, ( using any Ai chattool ex, CHatGPT ) . this period table would have 2 columns :
the column containning T3 , T6 and T12 and another column having the date .
so let us say your T3 corresponds to the last 3 months, T2 to the last 2 months and T12 to the last 12 months
then ask chatgpt to create for you such a table.
once created , add this table to your model .
link your period table ( created by chatgpt ) to your date table period with a many to many relationship ( using single direction from period to datetable ).
you may be wondering why many to many and 1 to many from period to datetable.
the reason is the dates for T2 , t3 and t12 are overlapping. this means, t12 contains the dates of t2 and t3, etcc.
but that would not harm your calculations in any way, since you will always be selecting only one value in your slicer, or none value, which in both cases will work.
hope that helps .
Great idea Daniel29195 !! Thank you so much!! I even added to the transaction table query to start the table on the last month we have data from instead of today
Really appreciate you taking the time and helping me out!I’m glad the idea helped.
