topic Re: Fill blank with previous non blank month's value in DAX Commands and Tips

Fill blank with previous non blank month's value

lucasneedhelp — Tue, 13 Jun 2023 08:59:00 GMT

Hi ALL,

I have a sample data like below and i'd like to fill blank with previous month's value by event ID and by Res Code

Event ID	Res Code	Date	Current Forecast
49327	AV	31/07/2020	80000
49327	AV	31/08/2020
49327	AV	30/09/2020
49327	AV	31/10/2020
49327	BEV	30/11/2020	82488.84
49327	BEV	31/12/2020
49327	BEV	31/01/2021
49327	BEV	28/02/2021	75000
49327	BEV	31/03/2021
49327	BEV	30/04/2021
49327	BEV	31/05/2021
56785	AV	30/06/2022	82488.84
56785	AV	31/07/2022
56785	AV	31/08/2022
56785	AV	30/09/2022	86210.93
56785	AV	31/10/2022
56785	BEV	30/11/2022	99171.04
56785	BEV	31/12/2022
56785	BEV	31/01/2023
56785	BEV	28/02/2023

my formular doesn't give me the desired result but very close. could someone please help!!

=
var vLastNonBlankDate=
CALCULATE(MAX('Mthly Calendar to FY25'[Date]),
FILTER(ALL('test grouped record'),
'test grouped record'[End of Month]<=MAX('Mthly Calendar to FY25'[Date]) &&
'test grouped record'[EOM Forecast.Current Forecast]<>0
)
)
var vLastNonBlankFcst=
CALCULATE(AVERAGE('test grouped record'[EOM Forecast.Current Forecast]),FILTER(ALL('Mthly Calendar to FY25'),'Mthly Calendar to FY25'[Date]=vLastNonBlankDate))
return
vLastNonBlankFcst

much appreciated!

Re: Fill blank with previous non blank month's value

Jihwan_Kim — Tue, 13 Jun 2023 11:53:03 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

Last Nonblank Forecast CC = VAR _lastnonblankdate = MAXX ( FILTER ( Data, Data[Event ID] = EARLIER ( Data[Event ID] ) && Data[Res Code] = EARLIER ( Data[Res Code] ) && Data[Date] <= EARLIER ( Data[Date] ) && Data[Current Forecast] <> BLANK () ), Data[Date] ) RETURN MAXX ( FILTER ( Data, Data[Event ID] = EARLIER ( Data[Event ID] ) && Data[Res Code] = EARLIER ( Data[Res Code] ) && Data[Date] = _lastnonblankdate ), Data[Current Forecast] )

Re: Fill blank with previous non blank month's value

lucasneedhelp — Tue, 13 Jun 2023 11:56:55 GMT

thank you so much, this is exactly what i'm looking for!

i'll go and test full data set

thank you

Re: Fill blank with previous non blank month's value

lucasneedhelp — Wed, 14 Jun 2023 01:26:56 GMT

Hi Jihwan,

when i try this formular in full data set it says
"This formula is invalid or incomplete: 'Calculation error in measure 'test grouped record'[test]: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.'."

and also i have a calendar table would that be an issue?

Re: Fill blank with previous non blank month's value

Jihwan_Kim — Wed, 14 Jun 2023 02:59:53 GMT

Hi,

1. Are you trying to create a calculated column? Or, a calculated measure?
2. Please share your sample pbix file's link here with how the expected result looks like, and then I can try to look into it.

Thank you.

Re: Fill blank with previous non blank month's value

lucasneedhelp — Wed, 14 Jun 2023 04:50:33 GMT

Hi Jihwan,

thank you so much for your help so far, please see link below

https://app.powerbi.com/links/XdHnCaToVy?ctid=97cda375-6766-40a9-a080-223aee73fae0&pbi_source=linkShare

there are three sets of data:

1. monthly calendar

2. sample data with full forecast history of 3 selected event ID

3. desired output for event ID49327 - this is manually entered to illustrate desired result

basically, i'd like to fill blank month with last month's value by Event ID, by Res Code. so I know at any point of time what is the total forecast amoutn for a certain event.

hope this makes sense

Regards

Lucas