Forum Discussion
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]
- Anonymous2 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
- AnonymousNot 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
- AmedvedFrequent 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.