topic Re: LASTNONBLANKVALUE doesn't work with other date columns beside the major calendar[Date] in DAX Commands and Tips

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

bubbledep — Fri, 05 Jul 2024 16:51:45 GMT

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.

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

Anonymous — Thu, 04 Jul 2024 06:51:16 GMT

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.

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

bubbledep — Thu, 04 Jul 2024 14:56:25 GMT

Hello,

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

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

Anonymous — Fri, 05 Jul 2024 01:01:11 GMT

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

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

bubbledep — Fri, 05 Jul 2024 01:13:53 GMT

so, what is the solution?

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

Anonymous — Fri, 05 Jul 2024 01:19:14 GMT

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.

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

sudhisami_azure — Fri, 05 Jul 2024 01:28:49 GMT

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

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

Anonymous — Fri, 05 Jul 2024 03:10:33 GMT

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.

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

bubbledep — Fri, 05 Jul 2024 14:06:21 GMT

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.

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

Anonymous — Mon, 08 Jul 2024 01:40:58 GMT

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.

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

bubbledep — Mon, 08 Jul 2024 12:08:39 GMT

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