Forum Discussion
Calculate DOH based on rolling month
Hi All,
I have following table:
| Report Month | V_Rolling_Sum | Attribute | Tdate | Value |
| 1/1/2020 | 50 | A | 31/12/2019 | 140 |
| 1/1/2020 | 150 | A | 31/12/2019 | 77 |
| 1/1/2020 | 75 | A | 30/11/2019 | 117 |
| 1/1/2020 | 50 | A | 30/11/2019 | 94 |
| 1/1/2020 | 300 | A | 30/10/2019 | 237 |
| 1/1/2020 | 375 | A | 30/10/2019 | 93 |
| 1/2/2020 | 525 | A | 10/23/2019 | 48 |
| 1/2/2020 | 650 | A | 10/29/2019 | 91 |
| 1/2/2020 | 150 | A | 31/12/2019 | 188 |
| 1/2/2020 | 600 | A | 31/12/2019 | 197 |
| 1/2/2020 | 550 | A | 30/11/2019 | 50 |
| 1/2/2020 | 100 | A | 30/10/2019 | 126 |
V_Rolling_Sum is rolling months sum for last 3 months. I need to create a new column- DH as below logic:-
for month - 1/1/2020 ->
=if(value<=Dec19, value/dec19 *31,if(Value<=(dec19+nov19),(Value-dec19)/nov19*(30+31)),if(Value<=(dec19+nov19+oct19),(Value-dec19-nov19)/oct19*(31+30+31)
Where 30 & 31 are numbers of days in that particular month.
ex 2:- > for month - 2/1/2020 -
=if(value<=jan20, value/jan20 *31,if(value<=(dec19+jan20),(value-jan20)/dec19*(31+31)),if(value<=(jan20+dec19+nov19),(value-jan20-dec19-nov19)/nov19*(31+30+31)
for reporting months the last 3 months taken into formula will change.
Urgent Help Required.!
HI Anonymous,
What the 'value' you mean? The 'rolling total' or 'value' field?
I use the value field to calculate the previous total results, you can take a look at the following calculated column formulas if it meets your requirement.DOH = VAR currDate = 'Table'[Report Month] VAR prev1Month = DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) ) VAR prev2Month = DATE ( YEAR ( currDate ), MONTH ( currDate ) - 2, DAY ( currDate ) ) VAR prev3Month = DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) ) VAR currTotal = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Report Month] = EARLIER ( 'Table'[Report Month] ) ) ) VAR P1MTotal = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Report Month] = prev1Month ) ) VAR P2MTotal = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Report Month] IN CALENDAR ( prev2Month, prev1Month ) ) ) VAR P3MTotal = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( 'Table', [Report Month] IN CALENDAR ( prev3Month, prev1Month ) ) ) RETURN IF ( currTotal <= P1MTotal, currTotal / P1MTotal * DAY ( EOMONTH(prev1Month,0)), IF ( currTotal <= P2MTotal, ( currTotal - P1MTotal ) / ( P2MTotal - P1MTotal ) * DAY ( EOMONTH(prev2Month,0)), IF ( currTotal <= P3MTotal, ( currTotal - P2MTotal ) / ( P3MTotal - P2MTotal ) * DAY ( EOMONTH(prev3Month,0)) ) ) )Regards,
Xiaoxin Sheng
4 Replies
Hi Anonymous,
I'd like to suggest you extract the current value and use date function to find the range of the last three months, it not need to consider the transfer about the end day of the month.
ROlling 3M Total = VAR currDate = TABLE[Tdate] RETURN CALCULATE ( SUM ( TABLE[Value] ), FILTER ( TABLE, [Tdate] >= DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) ) && [Tdate] <= EARLIER ( TABLE[Tdate] ) ) )Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng