cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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:

 

DATEVALUE
01/08/2018100
07/08/201860
10/08/201870

 

If I calculate AVERAGE, it will return 76.66.

 

But in fact, this table should be expanded to:

 

DATEVALUE
01/08/2018100
02/08/2018100
03/08/2018100
04/08/2018100
05/08/2018100
06/08/2018100
07/08/201860
08/08/201860
09/08/201860
10/08/201870
11/08/201870
12/08/201870
13/08/201870

 

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Calculate average filling values on missing dates

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

 

Try my new Power BI game Cross the River

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Calculate average filling values on missing dates

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

 

Try my new Power BI game Cross the River

View solution in original post

Highlighted
Super User III
Super User III

Re: Calculate average filling values on missing dates

@jrvidotti

 

Sample File attached

 

fill.png

Try my new Power BI game Cross the River
Highlighted
Frequent Visitor

Re: Calculate average filling values on missing dates

Very nice! Thank you very much!

Highlighted
Frequent Visitor

Re: Calculate average filling values on missing dates

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 appreciatedCattura.JPG

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors