Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

rolling 13 weeks in dax

Hi , I want to have rolling 13 weeks in a calculated column i.e current week plus future 12 weeks. I have to show other data accordingly.I have a date column created in calendar table which is cre...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    Please check the following steps as below.

     

    1. Create a calculated table and create two calcualted columns in it.

     

    dimtime = CALENDAR(MIN('sales'[date]),MAX('sales'[date]))

    weeknum = WEEKNUM('dimtime'[Date])
    year week = YEAR(dimtime[Date])*100+WEEKNUM(dimtime[Date])

    2. Create a new calculated table to get the result as we need.

     

    result = var yearweekt = YEAR(TODAY())*100+WEEKNUM(TODAY())
var yearweek12= YEAR(TODAY()+84)*100+WEEKNUM(TODAY()+84)
return
CALCULATETABLE(dimtime,yearweekt<=dimtime[year week] && dimtime[year week]<=yearweek12)

    For more details, please check the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous,

 

Please check the following steps as below.

 

1. Create a calculated table and create two calcualted columns in it.

 

dimtime = CALENDAR(MIN('sales'[date]),MAX('sales'[date]))

weeknum = WEEKNUM('dimtime'[Date])
year week = YEAR(dimtime[Date])*100+WEEKNUM(dimtime[Date])

2. Create a new calculated table to get the result as we need.

 

result = var yearweekt = YEAR(TODAY())*100+WEEKNUM(TODAY())
var yearweek12= YEAR(TODAY()+84)*100+WEEKNUM(TODAY()+84)
return
CALCULATETABLE(dimtime,yearweekt<=dimtime[year week] && dimtime[year week]<=yearweek12)

For more details, please check the pbix as attached.

 

Regards,

Frank

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Frank,

Thank you for the solution. I have one more thing, i am not able to put the weeks in order as 13 weeks , like 46,47,48,49,50,51,52,53,01,02,03,04,05. It is coming now as 01,02,03,04,05,46,47.....53. Is there any way possible that we can put them in order as i have shown above.

Thanks