Rolling sum sort of works, but presents the same number despite year.

[Zarlot531]

I'm trying to get a "rolling" measure by sold year/month. The below $841,469 correctly rolls 0 and 1 together (274,816+566,652) BUT it incorrectly (or unwantedly) puts that same number in each year, where, for 2016 for example i want (65,807 + 155,318 = 221,125) 

[v-frfei-msft · Accepted solution]

Hi @Zarlot531 ,

Please update your measure as below by adding VALUES function.

Rollingsum = 
SWITCH (
    SELECTEDVALUE ( Query1[Lag2] ),
    "1", CALCULATE (
        SUM ( Query1[RMR] ),
        FILTER ( ALL ( Query1 ), Query1[Lag2] = "0" || Query1[Lag2] = "1" ),
        VALUES ( 'Query1'[SoldMonth].[Year] )
    )
)

For more details, please check the pbix as attached.

[MFelix]

Hi @Zarlot531 ,

How are you calculating your information? What is the measure you are using for the rolling average?

Can you share a sample data?

Please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

Regards,

MFelix

[Zarlot531]

Below is a link to data. It's actually a rolling sum I'm looking for to eventually build a rolling % to total installed by month lag. 

Like below, the chart is constructed using similar data. The Power BI chart doesn't need to look exactly like this, but this is the basic idea. This is Orders by month but there's usually a lag for when the orders get "installed" or "implemented." I'd like to know, for example, that, say, 50% of our orders have been installed by month 3 instead of knowing what % happened in month 3 alone. To do this, I need rolling sums by month lag by month sold. Also, I've posted elsewhere but while everyone has been very helpful and I appreciate their help, nobody has been able to solve this conundrum. I've tried hard myself but just can't get there. 

https://community.powerbi.com/t5/Desktop/Rolling-sums-but-on-a-text-field/m-p/885456#M424381

https://www.dropbox.com/s/a5ppegph8bbgdik/DataShare.xlsx?dl=0 

[Zarlot531]

this has now been solved at the link I listed above.