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

[bubbledep]

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:

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

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.

[v-tianyich-msft]

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.

[bubbledep]

Hello,

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

[v-tianyich-msft]

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

[bubbledep]

so, what is the solution?

[v-tianyich-msft]

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.

[sudhisami_azure]

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

[v-tianyich-msft]

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.

[bubbledep]

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

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.

[v-tianyich-msft]

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]

"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.