Forum Discussion

naivecalf's avatar
naivecalf
Helper III
7 years ago
Solved

Expand rolling data

Hi,

 

I did rolling data by week, for qty_shipped, here is the fomular,

Accumulated Shipped = SUMX(
FILTER(
ALL(Shipped),
Shipped[Week_Shipped] >= 27 &&
Shipped[Week_Shipped]<= MAX(Shipped[Week_Shipped])),
Shipped[Amt_Shipped])
and here is the chart, please see green bar chart, how can I expand it to other date, in this sample, I need show 2.6M for weeks after and on 34.
thanks.
General Comment
Need Help

5 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    The condition stopping this data at week 33 is the following:

     

      Shipped[Week_Shipped]<= MAX(Shipped[Week_Shipped])),

     

    So you either need to just remove that conditition or maybe use the same max date as your ToCome , Backlog or Plan measures.

     

    • naivecalf's avatar
      naivecalf
      Helper III
      7 years ago

      d_gosbell 

       

      thank you. I tried to remove the condition, it expand to last week, but the total amount calculated a wrong number.

      • d_gosbell's avatar
        d_gosbell
        Super User
        7 years ago

        Oh yes, I see what that is doing. I don't normally use date columns directly in the fact table for filtering. I usually create a separate date table and then hide the date columns in the fact table. The date table should then have all the dates in the range you want to report on (so maybe all the days in 2019).

         

        Then you could build your calculations using the following pattern

         

        Accumulated Shipped = CALCULATE(
        SUM( Shipped[Amt_Shipped] ) ,
        FILTER(
        ALL(Date[Week]),
        Date[Week] >= 27 &&
        Date[Week]<= MAX(Date[Week]))
        )