Forum Discussion
Solved
Calculate average filling values on missing dates
How can I calculate an average of a value filling the missing dates, considering the last date value when non existant?
For example, on my table I have:
| DATE | VALUE |
| 01/08/2018 | 100 |
| 07/08/2018 | 60 |
| 10/08/2018 | 70 |
If I calculate AVERAGE, it will return 76.66.
But in fact, this table should be expanded to:
| DATE | VALUE |
| 01/08/2018 | 100 |
| 02/08/2018 | 100 |
| 03/08/2018 | 100 |
| 04/08/2018 | 100 |
| 05/08/2018 | 100 |
| 06/08/2018 | 100 |
| 07/08/2018 | 60 |
| 08/08/2018 | 60 |
| 09/08/2018 | 60 |
| 10/08/2018 | 70 |
| 11/08/2018 | 70 |
| 12/08/2018 | 70 |
| 13/08/2018 | 70 |
Note that if the last value isn't 0, it should continue calculating last date until today (10/08 -> 13/08 (today)).
The correct average will be 81.53 .
If I expand the table and fill the gaps from SQL Server, it will return 63 million rows with my server, so I think the best option is calculate it via DAX.
Hi,
Try this MEASUREMeasure = VAR temp = GENERATE ( Table1, GENERATESERIES ( [Date], VAR nextDateRow = TOPN ( 1, FILTER ( Table1, [DATE] > EARLIER ( [Date] ) ), [DATE], ASC ) VAR result = MINX ( nextDateRow, [DATE] ) RETURN IF ( result = BLANK (), TODAY (), result - 1 ) ) ) VAR temp1 = SELECTCOLUMNS ( temp, "Date", [Value], "Value", [VALUES] ) RETURN AVERAGEX ( temp1, [Value] )Or this calculated table
From Modelling Tab >>New Table
Table = VAR temp = GENERATE ( Table1, GENERATESERIES ( [Date], VAR nextDateRow = TOPN ( 1, FILTER ( Table1, [DATE] > EARLIER ( [Date] ) ), [DATE], ASC ) VAR result = MINX ( nextDateRow, [DATE] ) RETURN IF ( result = BLANK (), TODAY (), result - 1 ) ) ) RETURN SELECTCOLUMNS ( temp, "Date", [Value], "Value", [VALUES] )
4 Replies
Hi,
Try this MEASUREMeasure = VAR temp = GENERATE ( Table1, GENERATESERIES ( [Date], VAR nextDateRow = TOPN ( 1, FILTER ( Table1, [DATE] > EARLIER ( [Date] ) ), [DATE], ASC ) VAR result = MINX ( nextDateRow, [DATE] ) RETURN IF ( result = BLANK (), TODAY (), result - 1 ) ) ) VAR temp1 = SELECTCOLUMNS ( temp, "Date", [Value], "Value", [VALUES] ) RETURN AVERAGEX ( temp1, [Value] )Or this calculated table
From Modelling Tab >>New Table
Table = VAR temp = GENERATE ( Table1, GENERATESERIES ( [Date], VAR nextDateRow = TOPN ( 1, FILTER ( Table1, [DATE] > EARLIER ( [Date] ) ), [DATE], ASC ) VAR result = MINX ( nextDateRow, [DATE] ) RETURN IF ( result = BLANK (), TODAY (), result - 1 ) ) ) RETURN SELECTCOLUMNS ( temp, "Date", [Value], "Value", [VALUES] )Very nice! Thank you very much!
