Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Relative date dynamic table

Hi All,

 

I'm using an SSAS tabular model, and need a dynamic set of 13 sequential months MMM-YY that I can drag into the report as the X-axis of my graphs.

 

So, if my date slicer shows May-19, I would like the list of months to be from May-19 -> 13 months back to May-18

We don't want to have to pick all the months from the list, nor use the relative date slicer provided by PBI as the business want to have all of the graphs filtering on the same slicer.

I have at my disposal a full data warehouse Kimball style with a complete Date table and the Tabular model is the same. 

List of months

 

Thanks in advance!

Basia

Need Help

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

     

    You need a calendar table which is unlinked to fact table.

    Date = FILTER(CALENDAR(DATE(2018,1,1),DATE(2019,12,31)),DAY([Date])=1)

    Create below measures. Add measure [check] into visual level filter, and set its value to 1.

    Rank in calendar = RANKX(ALL('Date'),CALCULATE(MAX('Date'[Date])),,ASC,Dense)
Rank = RANKX(ALLSELECTED(Sample1),CALCULATE(MAX(Sample1[Date])),,ASC,Dense)
check = IF([Rank]>[Rank in calendar]-13&&[Rank]<=[Rank in calendar],1,0)

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-yulgu-msft ,

       

      Thank you very much for helping me with this. 

      Can you please clarify if the Date list on the left in your screenshot is from the existing Date Table with a join to fact or is it a list from the Unlinked Date table?

      Also, in your test model, what is the lowest grain of the new unlinked date table? Is it Day or Month?

      I'm giving this a go in SSAS, so far not much luck.

       

      Thanks again,

      Basia