cancel
Showing results for
Search instead for
Did you mean:
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:

 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
4 REPLIES 4
Highlighted
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
Highlighted
Super User III

## Re: Calculate average filling values on missing dates

@jrvidotti

Sample File attached

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 appreciated

## Helpful resources

Announcements

#### August 2020 Community Challenge: Can You Solve These?

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

#### Community Blog

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

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors