Filling the table with missing dates with the last known value

[piotrgrendus87]

Hello.
I have a problem with a way to create a measure that will allow me to fill in missing data dates with the last known values.

DateAverage

01/09/2022	0
01/10/2022	0
01/11/2022	0
01/12/2022	14.15
01/02/2023	34.84
01/03/2023	18.07
01/04/2023	38.39
01/05/2023	142.42

This is my orginal Data, with original Date and Average column. As You see there are missing months:

01/01/2023
01/06/2023

01/07/2023

01/08/2023
 

In my report I have calendar tabel with all the required dates:

01/09/2022

01/10/2022

01/11/2022

01/12/2022

01/01/2023

01/02/2023

01/03/2023

01/04/2023

01/05/2023

01/06/2023

01/07/2023

01/08/2023

and my goal is to have this result:

DateAverage

01/09/2022	0
01/10/2022	0
01/11/2022	0
01/12/2022	14.15
01/01/2023	14.15
01/02/2023	34.84
01/03/2023	18.07
01/04/2023	38.39
01/05/2023	142.42
01/06/2023	142.42
01/07/2023	142.42
01/08/2023	142.42

of course this data range will be changing every month.
Today is:
from 09/2022 to 08/2023, next month will be from 10/2022 to 09/2023 etc etc.

Can You support me how to create a correct metric to calculate this.

Regards Piotr.

[barritown]

Hi @piotrgrendus87,

Assuming that your Data and Calendar tables are not related, I can propose such a measure:

In plain text:

Value = 
VAR CurrentDate = MIN ('Calendar'[Date] )
VAR ExistingDate = MAXX ( FILTER ( Data, [Date] <= CurrentDate ), [Date] )
RETURN MAXX ( FILTER ( Data, [Date] = ExistingDate ), [Average] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian