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 0...
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] )
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!