Skip to main content
cancel
Showing results for 
Search instead 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

Reply
lucasneedhelp
Helper I
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 IDRes CodeDateCurrent Forecast
49327AV31/07/202080000
49327AV31/08/2020 
49327AV30/09/2020 
49327AV31/10/2020 
49327BEV30/11/202082488.84
49327BEV31/12/2020 
49327BEV31/01/2021 
49327BEV28/02/202175000
49327BEV31/03/2021 
49327BEV30/04/2021 
49327BEV31/05/2021 
56785AV30/06/202282488.84
56785AV31/07/2022 
56785AV31/08/2022 
56785AV30/09/202286210.93
56785AV31/10/2022 
56785BEV30/11/202299171.04
56785BEV31/12/2022 
56785BEV31/01/2023 
56785BEV28/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!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1686657135800.png

 

 

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.


Click here and Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
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.

 

Jihwan_Kim_0-1686657135800.png

 

 

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.


Click here and Go to My LinkedIn Page


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?

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.


Click here and Go to My LinkedIn Page


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 

 

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

i'll go and test full data set

thank you 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors