Forum Discussion

samdthompson's avatar
samdthompson
Memorable Member
9 years ago
Solved

Rolling 6 week average

Hi I think I am missing super simple here but I cannot get this working. I want to show a rolling 6 week average which is working and I want to show a rolling 6 week same period last year. I have the following measures working fine:

 

[This week]

=
ROUND ( SUMX ( Data, Data[Operating] ) / [Total KG], 2 )

 

[This week (LY)]

=
CALCULATE (
    [This week],
    ALL ( DatePeriod ),
    SAMEPERIODLASTYEAR ( 'Date'[Calendar_Date] )
)

 

[Rolling 6 wk operating price]

=
CALCULATE (
    [Operating avg $/kg],
    DATESINPERIOD (
        'Date'[Calendar_Date],
        LASTDATE ( 'Date'[Calendar_Date] ),
        -42,
        DAY
    )
)

 

when I do thie rolling 6 week last year calc it just fails on the continuous date periods but I cant figure out why. it shouldnt as far as I can tell.

 

[Rolling 6 wk operating price Data (LY)] 

=
CALCULATE (
    [This week (LY)],
    DATESINPERIOD (
        'Date'[Calendar_Date],
        LASTDATE ( 'Date'[Calendar_Date] ),
        -42,
        DAY
    )
)

 

Ideas appreciated

Need Help
  • samdthompson's avatar
    samdthompson
    5 years ago
    CALCULATE([Op $/kg],DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-42,DAY))

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi samdthompson

     

      Can you please provide some sample data like table names,column names with data.

     

    Regards,

    Hemant

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    samdthompson

     

    [Rolling 6 wk operating price Data (LY)] 
=
CALCULATE (
    [This week (LY)],
    DATESINPERIOD (
        'Date'[Calendar_Date],
        LASTDATE ( 'Date'[Calendar_Date] ),
        -42,
        DAY
    )
)

     

    The formula won't work because you use ALL function which will remove all filter contex for the table within the measure [This week (LY)], so the contex won't roll 6 week even though using DATESINPERIOD here.

     

    According to your description, the [Rolling 6 wk operating price Data (LY)] measure should be similar to the second measure [This week (LY)] you provided above. The formula below is for your reference.

    [Rolling 6 wk operating price Data (LY)] =
CALCULATE (
    [Rolling 6 wk operating price],
    SAMEPERIODLASTYEAR ( 'Date'[Calendar_Date] )
)

    Regards

  • samdthompson's avatar
    samdthompson
    Memorable Member
    5 years ago
    CALCULATE([Op $/kg],DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-42,DAY))