Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create filter using DIM_Date Table

Hi Experts

 

No sure if this is pretty striaght forward or more complex then i am making it out to be. I have a Dim-Date Table where the first column is the date column which cover a period (todays back to the last 5yrs).

 

I want to be able to create a slicer based on the date column where i can filter my data based on the last 7 Days, 30 Days and 60 days, using a slicer.

 

note the last 30 days must incl the last 7 days values too....

 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi Anonymous , 

    As I know, you could use slicer->relative date , which you could choose last N day without creating "Result column". In addition, you also could create last 7, last 30 's measure and use it when you choose this in slicer, you could refer to my sample for details.

    Best Regards,
    Zoe Zhi

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Refer, how to create a slicer to choose a measure

    https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

     

    Formula example

    Rolling 7 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-7,Day))  
Rolling 14 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-14,Day))
Rolling 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-30,Day))
Rolling 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-60,Day))

     

    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Amit

       

      thanks for the feedback but i am getting the following error message

      Error Message:

      MdxScript(Model) (63, 73) Calculation error in measure '_Measures'[Rolling 7]: An invalid numeric representation of a date value was encountered.

      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi Anonymous , 

        As I know, you could use slicer->relative date , which you could choose last N day without creating "Result column". In addition, you also could create last 7, last 30 's measure and use it when you choose this in slicer, you could refer to my sample for details.

        Best Regards,
        Zoe Zhi

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