Calculate DOH based on rolling month

[Anonymous]

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.!

[Anonymous]

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

[Anonymous]

Hi @Anonymous , 

Thankyou.

But i need the formula to calculate DH. 

Can you help me with the calculation for DH

[Anonymous]

If I use the above formula, it is also including the sum from later than 3 months. 

If anyone can please help.

[Anonymous · Accepted solution]

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