Wrong result when fill down with Lastnonblank dax

[Roy_W]

Hi all,

I meet the issue when deal the data with some date and milestone information.

First, I build two tables, one is the Calendar table, the other is about some date of milestone.

#Milestone table:

Then, I build relationship between the tables, and I need to fill down the milestone value to the Calendar table.

The dax formula as below:

rev_Milestone =

IF (

'Calendar'[Milestone] = "",

CALCULATE (

LASTNONBLANK('Calendar'[Milestone], 'Calendar'[Milestone]),

FILTER (

'Calendar',

'Calendar'[Date] < EARLIER('Calendar'[Date])

)

),

'Calendar'[Milestone]

)

The result shows wrong with m3 to finish:

I don't know why the formula goes wrong, please helps to figure out.

Another request for the formula is to stop fill down milestone value when detect "finish".

I have attached the file, thanks.

Sample File 

[Fowmy]

@Roy_W 


Can you check the attached file?

[Roy_W]

@Fowmy 
The result is not expected, I need the milestone change to next one,

The value under finish should be "finish" to the end, not "m3".

And another requirement is to stop fill down milestone value when detect "finish".

Thanks

[Greg_Deckler · Accepted solution]

@Roy_W 

Column = 
    VAR __Date = [Date]
    VAR __Max = MAXX(FILTER('Calendar',[Date]<=__Date && NOT(ISBLANK([Milestone]))),[Date])
    VAR __Milestone = MAXX(FILTER('Calendar',[Date]=__Max),[Milestone])
RETURN
    IF(__Milestone <> "finish",__Milestone,IF(__Max = __Date,__Milestone,BLANK()))

TI = bad.

[Roy_W]

@Greg_Deckler 

Thanks, issue solved 🙂

[Greg_Deckler]

@Roy_W You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Specifically: To **bleep** With FIRSTNONBLANK - Microsoft Power BI Community

These are based on this article:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...