Rolling Demand calcualtion based on the select Weeknum

[THENNA_41]

Month                          Weeknum                     DMDTotal         Inv
September 2024            WK352024                      200            4545
September 2024            WK362024                      200            4100
September 2024            WK372024                     200             4300
September 2024           WK382024                      200              4600
September 2024           WK392024                      200              2100
September 2024           WK402024                      200              2200
October 2024                WK412024                     250               2600
October 2024                 WK422024                    250               3600
October 2024              WK432024                      250              5600
October 2024                WK442024                    250              6600
November2024              WK452024                   200             1233
November2024                WK462024                 200             1233

I have the data till december 2025
Now if I am select any weeknum from selected weeknum to 12 weeks rolling DMDTOTal and selected Inv total and divided by 60 days (its static)

for example suppose I am select WK352024 then rolling 12 weeks to WK462024 total 2600
(WK352024,wk362024,wk372024,wk382024,wk392024,wk402024,wk412024,wk422024,wk432024,wk442024,wk452024,wk462024)

and selected week inv 4545

60 days static

then want calculation = 2600/(4545/60)

using dax ?

[Rupak_bi]

Hi @THENNA_41 ,

Here is your solution:

Rolling Demand =

Var Week_Num = WEEKNUM(max('Table'[Month]))

Var week_12 = Week_Num+12

Var inv= max('Table'[Inv])

Var dmd = sumx(filter(all('Table'),WEEKNUM('Table'[Month])>=Week_Num&&WEEKNUM('Table'[Month])<week_12),'Table'[DMDTotal])

return

dmd/(inv/60)

[THENNA_41]

@Rupak_bi  its not return a correct value .. Weeknum  its  text format 

[Rupak_bi]

OK, Then Please modify the DAX as below

Rolling Demand =

Var Week_Num = value(MID(max('Table'[Weeknum]),3,2))

Var week_12 = Week_Num+12 Var inv= max('Table'[Inv])

Var dmd = sumx(filter(all('Table'),value(MID(max('Table'[Weeknum]),3,2))>=Week_Num&&value(MID(max('Table'[Weeknum]),3,2))<week_12),'Table'[DMDTotal])

return

dmd/(inv/60)

Please let me know if it works. However, it is good to use date as index because when year will change, this formula will not work properly. So I suggest to use the previous DAX only and modify your Month column as date with 7 days interval for sustainable output.

[Irwan · Accepted solution]

hello @THENNA_41 

is this what you are looking for?

if yes, then:

1. create a calculated column for indexing. This indexing used for identifying when the Weeknum order (since week number is in middle character).

Index =
RANKX(
    'Table',
    'Table'[Weeknum],
    ,ASC,
    Dense
)

2. create measures with following DAX.

Selected INV = 
var _Inv = SELECTEDVALUE('Table'[Inv])
Return
IF(
    HASONEVALUE('Table'[Weeknum]),
    _Inv,
    ""
)

Sum DMDTotal =
var _MaxIndex = SELECTEDVALUE('Table'[Index])+12
var _MinIndex = SELECTEDVALUE('Table'[Index])
Return
IF(
    HASONEVALUE('Table'[Weeknum]),
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Index]<=_MaxIndex&&'Table'[Index]>=_MinIndex
        ),
        'Table'[DMDTotal]
    )
)

Calculation = 
DIVIDE(
    'Table'[Sum DMDTotal],DIVIDE(
        'Table'[Selected INV],
        60
    )
)

Hope this will help.

Thank you.