Struggeling with LastNonBlank and Rolling SUM

[jdat010]

Hi everyone!

Your help is really needed, so please have a look on my problem:

To understand the nature of the data better: It's planning stock data. The measure I need is an average (but must include blank values, so it might be better to take a sum and divide it by days)

The data is provided as snapshots, only values that changed compared to the last snapshot date.

Product SnapshotDate PlanDate Qty
A 01.11.2022 30.11.2022 50
A 05.11.2022 01.12.2022 150
A 07.11.2022 01.12.2022 170
A 07.11.2022 02.12.2022 20
A 08.11.2022 03.12.2022 30

1) the orange lines marks the lines where I need the last non blank qty, because this is the last row with the latest snapshot value

2) I need a rolling sum over the next 3 days (excluding the acutal day), regardless if there is a value or not, if there is not value, then it should be 0

3) the rolling sum needs to be divided by those 3 days to get an average

4) At the end the result in the visual should look like this, even with no filter set on one of the days:

I found already several examples and some got me half the way, but at the end it was always needed to show the plandate in the matrix or set at least a filter on the report.

 -> https://www.daxpatterns.com/week-related-calculations/ 

Has a good example in calculating a moving average (in the past), but one of the requirements is to include blank values, because there aren't rows on every single day.

[daXtreme]

Hi @jdat010 

The fact that your snapshots only contain data that's changed does not mean you can't make it into a full snapshot using Power Query. If you do, it'll be easier to work with this and your DAX will be easier and faster. Being good at Power BI does not mean being good at writing complex DAX. It means, first and foremost, being able to create models that are adequate to the tasks at hand (and hence simple). Then your DAX becomes simple and fast as well, which is what expertise means.

So, please work first on your model first and the rest will come naturally.