Max of 7 day rolling Sum

[tomkummerow]

Hi,

I'm wanting to create a measure to use in a table visual, which shows the Maximum value of the 7 day rolling sum over the previous 2 years worth of daily data for the field "Total Daily Redemption %NAV". This needs to be grouped/filtered by the "Aladdin Portfolio Name" field, so that I can see the maximum value of the 7 day rolling sum for each Portfolio. The DAX I'm currently using is 

Max 7 Day total = MAXX(
    CALCULATETABLE(
        SUMMARIZE(
            VALUES(Hist_Redemptions_VS_NAV[Aladdin Portfolio Name]), 
        Hist_Redemptions_VS_NAV[Aladdin Portfolio Name], "Max 7 Day total", Hist_Redemptions_VS_NAV[7 Day Total]), 
    DATESBETWEEN(Hist_Redemptions_VS_NAV[asofdate], MAX(Hist_Redemptions_VS_NAV[asofdate])-7, MAX(Hist_Redemptions_VS_NAV[asofdate]))), [Max 7 Day total])
 

Any help would be appreciated.

[v-cherch-msft]

Hi @tomkummerow 

You may try below measure.If it is not your case,please share some sample data and expected output which would be helpful to provide an accurate solution.You can also upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

How to Get Your Question Answered Quickly

ax 7 Day total =
MAXX (
    CALCULATETABLE (
        SUMMARIZE (
            VALUES ( Hist_Redemptions_VS_NAV[Aladdin Portfolio Name] ),
            Hist_Redemptions_VS_NAV[Aladdin Portfolio Name],
            "Max 7 Day total", Hist_Redemptions_VS_NAV[7 Day Total]
        ),
        DATESINPERIOD (
            Hist_Redemptions_VS_NAV[asofdate],
            MAX ( Hist_Redemptions_VS_NAV[asofdate] ),
            - 7,
            DAY
        )
    ),
    [Max 7 Day total]
)

Please have a look at below posts to check if they could help you.

https://community.powerbi.com/t5/Desktop/User-MEDIAN-of-rolling-7-day-total/td-p/369504

https://community.powerbi.com/t5/Desktop/Rolling-7day-Average-of-Sum/td-p/444842

Regards,

[tomkummerow]

Hi @v-cherch-msft, thanks for your response, unfortunately the DAX you gave gives the same result as the DAX I was already using.

I should also included the coding for the measure 7 Day total in my orignal post.

7 Day Total = Calculate(
[Total Redemptions], 
DATESINPERIOD(
Hist_Redemptions_VS_NAV[asofdate], 
LASTDATE(
Hist_Redemptions_VS_NAV[asofdate]), -7, DAY))

Thanks

Tom

[v-cherch-msft · Accepted solution]

Hi @tomkummerow 

You may not use Time-intelligence function .I would suggest you create an index column first.Then create a measure to get the sum when index from 1 to 7.

Index =
RANKX (
    FILTER (
        Table1,
        Table1[Aladdin Portfolio Name] = EARLIER ( Table1[Aladdin Portfolio Name] )
    ),
    Table1[asofdate],
    ,
    ASC
)

Measure  =
CALCULATE (
    [Total Redemptions],
    FILTER (
        ALLEXCEPT ( Table1, Table1[Aladdin Portfolio Name] ),
        Table1[Index] >= MIN ( Table1[Index] )
            && Table1[Index]
                <= MIN ( Table1[Index] ) + 6
    )
)

Regards,