Filling the table with missing dates with the last known value

piotrgrendus87 — Thu, 28 Sep 2023 10:00:55 GMT

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.

Re: Filling the table with missing dates with the last known value

barritown — Mon, 02 Oct 2023 10:37:49 GMT

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

topic Filling the table with missing dates with the last known value in DAX Commands and Tips

Filling the table with missing dates with the last known value

Re: Filling the table with missing dates with the last known value