Calculate average filling values on missing dates

[jrvidotti]

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.

[Zubair_Muhammad · Accepted solution]

@jrvidotti

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] )

[jrvidotti]

Very nice! Thank you very much!

[Zubair_Muhammad]

@jrvidotti

Sample File attached

[Anonymous]

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