Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cumulative Sum, reset by x period

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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
))

View solution in original post

7 REPLIES 7
drewschbag
Regular Visitor

Have you tried simply binning by 28 day increments?

Anonymous
Not applicable

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

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
Not applicable

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.

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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
))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.