Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bubbledep
Frequent Visitor

LASTNONBLANKVALUE doesn't work with other date columns beside the major calendar[Date]

Hi everyone,

 

I'm having a trouble when I try to filldown some values. Everything works fine when I use my major calendar[Date] as the date column within the table, but when I try to use an "DD/MM" formated column, my filled rows doesn't work and goes blank (I don't know if that's related to multiple days/months from different years and that's why it keeps showing values from different years inside the same month from those different days).

 

Here's the result expected:

bubbledep_0-1720034912262.png

 

Here's the result I'm getting when using the "DD/MM" formated column:

bubbledep_0-1720018261752.png

 

Here below I share you my filldown measure:

 

IF(ISBLANK(AVERAGE(Table[Value])),
    LASTNONBLANKVALUE(
        FILTER(ALL('.dCalendar'[Date]),'.dCalendar'[Date]<=MAX('.dCalendar'[Date])),
        CALCULATE(AVERAGE(Table[Value]))),
    AVERAGE(Table[Value])
)

 

I already tried some solutions like using more then one relationship with the USERELATIONSHIP function and changing the LASTNONBLANKVALUE logic, but none of them worked.

 

Thanks in advance.

10 REPLIES 10
v-tianyich-msft
Community Support
Community Support

Hi @bubbledep ,

 

Please check that after you change the format, its data type is still a date type.

 

Please feel free to correct me and provide more information if I have misunderstood you!

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

 

The "DD/MM" column is text format yes.

Hi @bubbledep ,

 

Because the text format is not available for this part of the expression to be compared, it will result in a blank return:

'.dCalendar'[Date]<=MAX('.dCalendar'[Date])

 

Best regards,
Community Support Team_ Scott Chang

so, what is the solution?

Hi @bubbledep ,

 

Add an index column via power query to change the date in the expression to index.

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

How the Index column will work in this scenario? Could you provide info, please

Hi @sudhisami_azure ,

 

Go to the power query screen through transform data and click on Add column and it will add a column Index and then click on close and apply. and use the following expression:

IF(ISBLANK(AVERAGE(Table[Value])),
    LASTNONBLANKVALUE(
        FILTER(ALL('.dCalendar'[Index]),'.dCalendar'[Index]<=MAX('.dCalendar'[Index])),
        CALCULATE(AVERAGE(Table[Value]))),
    AVERAGE(Table[Value])
)

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

I already have an IndexColumn which I made using RANKX, but the problem is that I can't relate my DD/MM column to sort by that IndexColumn or something like that. I need the DD/MM formated column as my x-axis, that's the goal.

Since I have multiples DD/MM's which can repeat in all year from my calendarTable, I can't sort my DD/MM formated column by any calendar[Date] or IndexColumns, because DD/MM can repeat tru years, meanwhile it's not allowed for calendar[Date] and IndexColumn to have repeated values.

In order to exemplify, I share below the IndexColumn that I already had, which works just fine to fill my logic:

 

IF(ISBLANK(AVERAGE(Table[Value])),
    LASTNONBLANKVALUE(
        FILTER(ALL('.dCalendar'[Index]),'.dCalendar'[Index]<=MAX('.dCalendar'[Index])),
        CALCULATE(AVERAGE(Table[Value]))),
    AVERAGE(Table[Value])
)

 

bubbledep_0-1720188221173.png


Also my other measures related to this visual (which I use calendar[Date]) doesn't break since my IndexColumn is related to my calendar[Date], and that's awesome. But my problem still, because I don't need to use an IndexColumn as my x-axis, I need to use a DD/MM formated column as my x-axis instead.

Hi @bubbledep ,

 

Put the DD/MM into X-axis and then use sort by column and select index in there to achieve your desired result.

Sort one column by another column in Power BI - Power BI | Microsoft Learn

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

bubbledep_0-1720440320092.png

"Date_Total_Day" is my index column that I created using RANKX formula and my Date DD/MM column is formatted as "FORMAT(calendar[Date], "DD/MM")". But since I have repeated dates from different years (date 01/02 from years 2022, 2023 and 2024 for example), I'm unable to sort by my index column because can't have repeated values in there like in my Date DD/MM formated column.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors