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.