Lastnonblank and average on rolling sum

[jdat011]

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 !

[amitchandak]

@jdat011 ,

Lastnonblank(Table[PlanDate], Sum(Table[QtyPlanned])

Average(Table[QtyPlanned]) 

or

calculate(Average(Table[QtyPlanned])  allselected())