Hello everybody,
I am using power pviot to analyze data about daily stock (daily inventory level).
The input are the month opening inventory and the daily inventory movements.
Inventory at day x is given by a calculated field:
calculate(sum(Main_Analys_Matrix[M15]),DATESMTD(DATEADD(Main_Analys_Matrix[Date],-1,day)))
+0
+calculate(sum(Main_Analys_Matrix[Opening_SAP_D_1]),DATESMTD(DATEADD(Main_Analys_Matrix[Date],0,day)))
The first calculate sum the opening inventory up to the day x (opening inventory has a value at day 1 of the month and zero the others day)
The second calculate sum the inventory movements up to the day x
+0 is just a trick to have zero instead no value in the pivot tables
Formula works, and caluculate the daily inventory.
Now I would like to calculate the month average of this caluculated field (in order to add a orizontal line in the charts). I tried various formulas but I could not meet my objetive (SUMX, AVERAGEX, CALCULATE seems don't work if they act on a calculated field).
Do you have any suggetions?.
Thanks
