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

4 Replies

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Wagwo0rd4powerb ,

       

      Try replacing blank with 0. via power query if the connection mode supports it.

       

      Best regards,
      Community Support Team_ Scott Chang