Forum Discussion

hello_MTC's avatar
hello_MTC
Icon for Helper III rankHelper III
4 years ago
Solved

Slicer for Last 6 Months

 Hello,

 

I want to create a sclicer for "Last 6 Months", "Last 3 Months". I've columns ready as below. I need to submit this in 24hrs. Help will be appreciated.

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi hello_MTC ,

     

    1. Create a table for slicer:

    2. Add a flag measure:

    Flag = 
var _diff= DATEDIFF(MAX('Table'[Date]),TODAY(),DAY)
return SWITCH(MAX('For Slicer'[Value]),"Last 3 Months", IF(_diff>=0 && _diff<=90,1,0),"Last 6 Months", IF(_diff>=0 && _diff<=180,1,0))

    3.Apply it to visual-level filter pane:

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

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi hello_MTC ,

     

    So it depends on which date you want to be based on.

     

    For example: you could replace TODAY() with MAXX(ALL('Table'),[Date]).

    Table = CALENDAR(DATE(2021,9,1),DATE(2022,4,30))
    Flag = 
var _maxDate=MAXX(ALL('Table'[Date]),[Date])  // based on the lateset date in Table
var _diff= DATEDIFF(MAX('Table'[Date]),_maxDate,DAY)
return SWITCH(MAX('For Slicer'[Value]),"Last 3 Months", IF(_diff>=0 && _diff<=90,1,0),"Last 6 Months", IF(_diff>=0 && _diff<=180,1,0))

     

     

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

13 Replies

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    Hi hello_MTC ,

     

    Add two columns with in Power Query or Power Pivot by using if formula:

     

    if date is less (than today's date - 90 days / 180 days) then 3 months / 6 months then null / ""

     

    Add slicer with newly added column then go to settings, tick hide item with no data,

     

    Regards

    KT 

    • hello_MTC's avatar
      hello_MTC
      Icon for Helper III rankHelper III
      4 years ago

      Thank you for your reply. it is highly appreciated

      Can you please write a proper DAX function here. It will be more helpful.

      • KT_Bsmart2gethe's avatar
        KT_Bsmart2gethe
        Icon for Impactful Individual rankImpactful Individual
        4 years ago

        Hi hello_MTC ,

         

        Would you kindly share some sample data with sensitive information removed? I will get back to you with the formula. It does help to have your question resolved quicker.

         

        Regards

        KT

    • hello_MTC's avatar
      hello_MTC
      Icon for Helper III rankHelper III
      4 years ago

      However, I wrote this 

      LastMonths = IF('uat_db incident_condition_template'[updated_at]<TODAY()-90,"Last 3 Months",IF('uat_db incident_condition_template'[updated_at]<TODAY()-180,"Last 6 Months"))
      And I have Jan, Feb, Mar and Apr data in this column. I got this.
       

       

      Is it True?

    • hello_MTC's avatar
      hello_MTC
      Icon for Helper III rankHelper III
      4 years ago

      What If I want only Last 6 Months from current date only. Remove Last 3 Months

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi hello_MTC ,

     

    1. Create a table for slicer:

    2. Add a flag measure:

    Flag = 
var _diff= DATEDIFF(MAX('Table'[Date]),TODAY(),DAY)
return SWITCH(MAX('For Slicer'[Value]),"Last 3 Months", IF(_diff>=0 && _diff<=90,1,0),"Last 6 Months", IF(_diff>=0 && _diff<=180,1,0))

    3.Apply it to visual-level filter pane:

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

    • hello_MTC's avatar
      hello_MTC
      Icon for Helper III rankHelper III
      4 years ago

      Hi, this is great but i'm facing small issue here.

      Whenever I select filter = 1 in filter panel it doesn't show me data of 2021.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi hello_MTC ,

         

        Because my measure is based on Today(2022/July) , so for last 6 months, the minimum  month will be 2022/February

         

        Best Regards,
        Eyelyn Qin

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi hello_MTC ,

     

    So it depends on which date you want to be based on.

     

    For example: you could replace TODAY() with MAXX(ALL('Table'),[Date]).

    Table = CALENDAR(DATE(2021,9,1),DATE(2022,4,30))
    Flag = 
var _maxDate=MAXX(ALL('Table'[Date]),[Date])  // based on the lateset date in Table
var _diff= DATEDIFF(MAX('Table'[Date]),_maxDate,DAY)
return SWITCH(MAX('For Slicer'[Value]),"Last 3 Months", IF(_diff>=0 && _diff<=90,1,0),"Last 6 Months", IF(_diff>=0 && _diff<=180,1,0))

     

     

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

    • hello_MTC's avatar
      hello_MTC
      Icon for Helper III rankHelper III
      4 years ago

      It worked and I Accepted as Solution. But something is missing here. The Slicer is not working because it doesn't have any relationship with my other table.
      ?

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    Hi hello_MTC ,

     

    I forgot to mentioned if it is in Power BI, you can simply untick the items from the filter and it will not appear in the slicer.