Hi all,
I posted some minutes ago, but my post is not showing up so it might be ended in the spam 😞
Im trying to create a measure based on planning stock data. I already created a last non blank one for the qtyPlanned. But I need another one which is a rolling average over the last 3 PlanDates. The average needs to include blank values, so it might be better to have a rolling sum of the last non empties and then divide by 3 days.
| Product | Snapshotdate | PlanDate | QtyPlanned | LastNonEmpty |
| A | 2022-11-01 | 2022-11-30 | 50 | x |
| A | 2022-11-05 | 2022-12-01 | 150 | |
| A | 2022-11-07 | 2022-12-01 | 170 | x |
| A | 2022-11-07 | 2022-12-02 | 20 | x |
| A | 2022-11-08 | 2022-12-03 | 30 | |
The lastNonEmpty column is just to show which rows I need, the column shouldnt be needed in the dataset.
The result should look like this:
| Product | LastPlanValue | AveragePlanValue |
| A | 30 | 80 |
The average value should work, even wheren there is no filter set on PlanDate.
I already had a lot of research and I made it to create a very complicated measure which is working as long as I have the PlanDate in the report, but the value is always empty when I remove the PlanDate as a grouping or filter.
Thanks for reading, any help is appreciated !
