cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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]``````

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thanks very much @v-yiruan-msft . 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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.