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, ...
Need Help
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