Forum Discussion

Amedved's avatar
Amedved
Frequent Visitor
2 years ago
Solved

Creating a Rolling, Cumulative Metric in a Matrix Visual

Hi! I am trying to create a measure to add to my matrix visual that is a rolling total. This "Backlog" measure should subtract the current day's "Output" from the prior day's backlog plus the current day's "RemainingQtyTotal". The measure works for some days, but not for most of them. In the below screenshots, for 4/25 the backlog should be (404.7+18)-10.5=412.2. And other days don't subtract the Output at all, like 5/21. Here is a link to my Pbix file. Below are my current formula plus screenshots for reference. Appreciate any help!

 

 

 

Backlog = 
VAR CurrentDate = MAXX(ALLSELECTED(Orders),Orders[PlanDate])
RETURN (IF (MAX('Date'[Date]) <= CurrentDate,
CALCULATE(
    [RemainingQtyTotal],
    FILTER(
       ALLSELECTED('Date'[Date]),
        ISONORAFTER('Date'[Date],MAX('Date'[Date]),DESC)
    )
),blank()
))
-[Output]

 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Amedved ,

    I updated your sample pbix file, please find the details in the attachment.  You can create a new calculation to get it:

    Calculation = PREVIOUS([Backlog])+[RemainingQtyTotal]-[Output]

    Best Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Amedved ,

    I updated your sample pbix file, please find the details in the attachment.  You can create a new calculation to get it:

    Calculation = PREVIOUS([Backlog])+[RemainingQtyTotal]-[Output]

    Best Regards

    • Amedved's avatar
      Amedved
      Frequent Visitor

      Thanks very much Anonymous . I was not familiar with visual calculations. I took your suggested approach and got to the desired outcome after I adjusted the formula a bit to meet some requirements. Thanks again for the assist.