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...
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.

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.