Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
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
User | Count |
---|---|
65 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |