AVERAGE If not every dataset exists

[Jayshamone]

Hello,

I would like to calculate an average value for our product stock.

However we do not have a value for each day, but only for each day with a change.

It would be possible, but highly memory-intensive to precalculate all non-existent values.

Is there a possibility to add the last value using DAX syntax.

The table below shows how it should be calculated. 

Cum_sum = 66

Avg := 66 / 7 = 9,43

Date	Value	Cum_Sum
01.06.	7	7
02.06.	8	15
03.06		23
04.06.	9	32
05.06.	13	45
06.06.		58
07.06.	8	66

Thank you for your ideas.

Justus

[ryan_mayu · Accepted solution]

@Jayshamone 

pls see the attachment below

[Jayshamone]

@ryan_mayu Thanks for the file.

However the server needs to store all of the Information.

We store 1 dataset per article (roughly 30.000) for each store (35) per day (6 months).
In order to even have a dataset for articles that haven't changed stock for a long time, we also store the last value that was longer than 6 months ago.

This means we would have to store an additional minimum of 189 Million - in reality more than a billion datasets on the server. 

Would it be possible to just do the calculation using DAX?

[V-lianl-msft]

Similar methods are based on the evolution of @ryan_mayu  method. Based on what you said, using powerbi to process such a large data set is not his strong point. It is recommended that you use powerbi to visualize the data after processing the data at the data source.