Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Power BI Slicer 12 months automatic selection

Hi,

I have a slicer with YYYYMM values and it contains data for multiple years and new data is loaded each month. Currently as per requirement I have manually selected latest 12 months( From 202109 to 202208) and published the report. I need to automate this, in this case once 202209 data is loaded in report slicer automatically picks new recent 12 months i.e., from 202110 to 202209. Please help me on this or any work around.

 

 

Tagging some of the known experts : amitchandak  Greg_Deckler  parry2k 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    Please try this custom visual:

    Preselected Slicer

     

    And use measure like this as the Pre selection:

     

    _Month = 
VAR __Month_1 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1), "YYYYMM")
VAR __Month_2 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-2,1), "YYYYMM")
VAR __Month_3 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,1), "YYYYMM")
VAR __Month_4 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-4,1), "YYYYMM")
VAR __Month_5 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-5,1), "YYYYMM")
VAR __Month_6 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-6,1), "YYYYMM")
VAR __Month_7 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-7,1), "YYYYMM")
VAR __Month_8 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-8,1), "YYYYMM")
VAR __Month_9 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-9,1), "YYYYMM")
VAR __Month_10 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-10,1), "YYYYMM")
VAR __Month_11 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-11,1), "YYYYMM")
VAR __Month_12 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-12,1), "YYYYMM")
VAR __SelectedMonth = SELECTEDVALUE('Date'[Year Month])
RETURN 
    __SelectedMonth IN {__Month_1,__Month_2,__Month_3,__Month_4,__Month_5,__Month_6,__Month_7,__Month_8,__Month_9,__Month_10,__Month_11,__Month_12}

     

    result:

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , No(we need work around), we have to create a text like the last 12 months and use that in the selection and that will change automatically

     

    a new column in table

     

    lasr 12 Months Type = Switch( True(),
    ([Date]) >= eomonth(Today(),-11) && Date([Date])<= eomonth(Today(),0) ,"Last 12 Months" , //Last Month // or use -12
    ([Date])= eomonth(Today(),0),"Other Months" , //This Month
    [Month Year]
    )

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    Please try this custom visual:

    Preselected Slicer

     

    And use measure like this as the Pre selection:

     

    _Month = 
VAR __Month_1 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-1,1), "YYYYMM")
VAR __Month_2 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-2,1), "YYYYMM")
VAR __Month_3 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-3,1), "YYYYMM")
VAR __Month_4 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-4,1), "YYYYMM")
VAR __Month_5 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-5,1), "YYYYMM")
VAR __Month_6 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-6,1), "YYYYMM")
VAR __Month_7 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-7,1), "YYYYMM")
VAR __Month_8 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-8,1), "YYYYMM")
VAR __Month_9 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-9,1), "YYYYMM")
VAR __Month_10 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-10,1), "YYYYMM")
VAR __Month_11 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-11,1), "YYYYMM")
VAR __Month_12 = FORMAT(DATE(YEAR(TODAY()),MONTH(TODAY())-12,1), "YYYYMM")
VAR __SelectedMonth = SELECTEDVALUE('Date'[Year Month])
RETURN 
    __SelectedMonth IN {__Month_1,__Month_2,__Month_3,__Month_4,__Month_5,__Month_6,__Month_7,__Month_8,__Month_9,__Month_10,__Month_11,__Month_12}

     

    result:

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data