Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 ))
Have you tried simply binning by 28 day increments?
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/
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.
I'll take a look thanks. Function sseems similar to Excel, which I can deal with..
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 ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |