Forum Discussion

Wagwo0rd4powerb's avatar
Wagwo0rd4powerb
Regular Visitor
2 years ago

Calculating 3 Month rolling total not working when value is blank

Example of my code:   Oct2023 is blank and therefore no value displays. Please advise.
Need Help
Wagwo0rd4powerb's avatar
Wagwo0rd4powerb
Regular Visitor
2 years ago

This also does not work... 

 

3 Months CaseCounQTY Rolling Total = CALCULATE(SUM(MDSProduction[Amount Case Count])+0,
DATESINPERIOD(MDSProduction[Month Year Date],LASTDATE(MDSProduction[Month Year Date]),-3,MONTH)) 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Wagwo0rd4powerb ,

 

Try using the following expression:

 

3MonthRollingTotal = 
CALCULATE(
    SUMX(
        Table,
        IF(ISBLANK(Table[Value]), 0, Table[Value])
    ),
    DATESINPERIOD(
        Table[Date],
        LASTDATE(Table[Date]),
        -3,
        MONTH
    )
)

 

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.