Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi,
Try this MEASURE
Measure = 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] )
Hi,
Try this MEASURE
Measure = 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!
Sample File attached
Hi, this is exactly what I need, but I get this error and I have no idea what the issue is. I'm really new and strugglying a lot here, any help is much appreciated
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |