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 created from a date column from other table. When I am using the query editor filter "IN the next 12 weeks" , the data is coming incorrect. Please help me solving this issue.

Thanks

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

4 Replies

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

    Hi Anonymous,

     

    Could you please share your sample data and excepted result to me?

     

    Regards,

    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Frank,

      I have a calendar table made out of a minimum and maximum due date from sales table. This table contains date for 2 quarters at one time and keep updating regularly eg Q4-18 and Q1-19, Q1-19 and Q2-19, Every quarter the dates update. I need to make a week column in the Calendar table which gives me current week and 12 weeks as the output irresepective of the year. 
      For example i want output  currently as Wk 46,47,48,49,50,51,52,53,01,02,03,04,05(I have dates from 1st oct-2018 to 31st march 2019 in calendar table).  Next week it should be changed to Wk 47till wk 06. 

      Thanks

      • 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