Forum Discussion
Helper IAVERAGE If not every dataset exists
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
pls see the attachment below
3 Replies
ryan_mayuSuper User
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-msftCommunity Support
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.