Fill blank with previous non blank month's value

[lucasneedhelp]

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!

[Jihwan_Kim · Accepted solution]

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]
    )

[lucasneedhelp]

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?

[Jihwan_Kim]

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. 

[lucasneedhelp]

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=linkSh...

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 

[lucasneedhelp]

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

i'll go and test full data set

thank you