cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Fill blank with previous non blank month's value

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

=
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!

1 ACCEPTED SOLUTION
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

5 REPLIES 5
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper I

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?

Super User

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.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper I

Hi Jihwan,

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

Helper I

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

i'll go and test full data set

thank you