Cumulative Sum, reset by x period

[Anonymous]

I have a function for a cumulative sum as follows:

Actions closed cumulative =
IF(MAX(Dates[Date])<CALCULATE(MIN(Dates[Date]),
		FILTER(all(Dates),Dates[PeriodFiscalYear]=[CurrentPeriod1])),
		CALCULATE(Actions[Actions closed on time],
			FILTER(ALL(Dates),
			Dates[Date]<=max(Dates[Date])
							)
))

Which returns something like this:

PeriodFiscalYear | Actions | Actions Closed Cumulative
P01-2018/19      | 4            | 608
P02-2018/19      | 19          | 627
P03-2018/19      |  17         | 644        
P04-2018/19      |  6           | 650
P05-2018/19      |  7           | 657

So it's basically counting all the actions closed in the table at the moment but I'd like to reset on a certain number of periods, for example 3 periods would be:

PeriodFiscalYear | Actions | Actions Closed Cumulative

P12-2017/18      | 10          |

P13-2017/18      | 10          | 

P01-2018/19      | 4            | 24
P02-2018/19      | 19          | 33
P03-2018/19      |  17         | 40      
P04-2018/19      |  6           | 42
P05-2018/19      |  7           | 30

I'm struggling to understand how to do it, despite quite a lot of reading. I have a calendar table with dates by 13 periods per year and also pretty much every measure you could think of, month, monthyear, monthperiod etc etc. Any help would be appreciated. Ultimate goal is a moving average over a set number of periods.

Thanks

[drewschbag]

Have you tried simply binning by 28 day increments?

[Anonymous]

Can't do it I am afraid (not that I know how) - our P1  and P13 are non-standard length as MOD(365,28)<>0...

I do have a field for length of period in my calendar table.

I was wondering if a period offset column would help? e.g. find the current period, call that 0 then the last one is -1 etc etc

[drewschbag]

I ended up doing something similar with rolling weeks.

Something like:

Rolling Prior Week Total =
VAR filterTime = SELECTEDVALUE('Dates'[week index], BLANK())
RETURN CALCULATE( [Total], ALL( 'Dates'[Date] ), 'Dates'[week index] = filterTime - 1 )

See also:

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/ 

[Anonymous]

Sorry, I realised I hadn't stated my question accurately - what I need is a rolling cumulative sum over x period. I think you got my drift anyway!

So in your reply, if you wanted the previous x weeks, you'd have:

Rolling Prior Week Total =
VAR filterTime = SELECTEDVALUE('Dates'[week index], BLANK())
RETURN CALCULATE( [Total], ALL( 'Dates'[Date] ), 'Dates'[week index] >= filterTime - x)

How did you define your week index column? I'll check the article anyway- thanks.

[drewschbag]

https://powerbi.tips/2017/11/creating-a-dax-calendar/ 

[Anonymous]

I'll take a look thanks. Function sseems similar to Excel, which I can deal with..

[Anonymous · Accepted solution]

I figured out that this does the job for the period offset:

PeriodOffset =

(Dates[Period]+(Dates[FiscalYear]*13))-

(CALCULATE(VALUES(Dates[Period]),
			Dates[Date]=TODAY())+
(CALCULATE(VALUES(Dates[FiscalYear]),
			Dates[Date]=TODAY())*13
))