topic Re: sum the last nonblank value from multiple columns in DAX Commands and Tips

sum the last nonblank value from multiple columns

Anonymous — Tue, 15 Apr 2025 10:56:15 GMT

hello

i want to sum the last non blank values from the three columns as figured in below pictures. green column

i tried this formula, it gave me the the result in column TOTA MP PLAN

MP Plan =

VAR cal_type = "Plan"

VAR cal_item = "ManPower"

VAR last_date =

CALCULATE (

LASTNONBLANK (

Schedules_Progress[Date],

COUNT ( Schedules_Progress[Value] )

Schedules_Progress[Item] = cal_item,

Schedules_Progress[Type] = cal_type

)

VAR TBL =

CALCULATETABLE ( VALUES ( CalendarData[DateID] ) )

VAR result =

CALCULATE (

SUMX (

VALUES ( SITE_MOC_ID[MOC_ID] ),

CALCULATE (

LASTNONBLANKVALUE (

Schedules_Progress[Date],

MAX ( Schedules_Progress[Value] )

)

FILTER (

ALL ( CalendarData[DateID] ),

CalendarData[DateID]

<= MAX ( CalendarData[DateID] )

Schedules_Progress[Item] = cal_item,

Schedules_Progress[Type] = cal_type

)

RETURN

IF ( MAX ( CalendarData[DateID] ) = last_date, result, BLANK () )

thanks

Re: sum the last nonblank value from multiple columns

bhanu_gautam — Tue, 15 Apr 2025 12:05:04 GMT

Anonymous Create a measure to get the last non-blank value for each column.

DAX
LastNonBlankValue_Column1 =
CALCULATE(
LASTNONBLANKVALUE(
Schedules_Progress[Date],
MAX(Schedules_Progress[Column1])
)
)

LastNonBlankValue_Column2 =
CALCULATE(
LASTNONBLANKVALUE(
Schedules_Progress[Date],
MAX(Schedules_Progress[Column2])
)
)

LastNonBlankValue_Column3 =
CALCULATE(
LASTNONBLANKVALUE(
Schedules_Progress[Date],
MAX(Schedules_Progress[Column3])
)
)

SumLastNonBlankValues =
[LastNonBlankValue_Column1] + [LastNonBlankValue_Column2] + [LastNonBlankValue_Column3]

Re: sum the last nonblank value from multiple columns

sjoerdvn — Tue, 15 Apr 2025 12:40:57 GMT

I think you might be overcomplicating things, try this:

MP Plan = VAR cal_type = "Plan" VAR cal_item = "ManPower" VAR last_date = MAX ( CalendarData[DateID]) RETURN SUMX ( VALUES ( SITE_MOC_ID[MOC_ID] ), CALCULATE ( LASTNONBLANKVALUE ( CalendarData[DateID], MAX ( Schedules_Progress[Value] ) ), ALL ( CalendarData ), CalendarData[DateID] <= last_date, Schedules_Progress[Item] = cal_item, Schedules_Progress[Type] = cal_type ) )

Re: sum the last nonblank value from multiple columns

Anonymous — Tue, 15 Apr 2025 13:13:46 GMT

thanks sjoerdvn

your formulat is the logical one.

but my issue here is that i dont want the calculation to go beyond the last value for each column.

so i want to calculate the sum of last nonbank value since there a value after (just to cover the gap between values)

example :

MOC-0002-K should be not calculated after 02-Sep becasue value 01 is last one.

MOC-0001-L should be not calcalated after 02-Dec becasue last value is 01

thanks a lot

Re: sum the last nonblank value from multiple columns

Anonymous — Tue, 15 Apr 2025 13:20:11 GMT

sjoerdvn let me draw your attention that the issues start on 9 Sep and after

Re: sum the last nonblank value from multiple columns

sjoerdvn — Tue, 15 Apr 2025 13:30:19 GMT

Ah, so I misunderstood what you were trying to achieve. So you need the LastNonBlank to fill the gaps, but you want to keept the blank after the last occurrence. I think you could simply add an IF, like below.

MP Plan = VAR cal_type = "Plan" VAR cal_item = "ManPower" VAR last_date = MAX ( CalendarData[DateID]) RETURN SUMX ( VALUES ( SITE_MOC_ID[MOC_ID] ), IF(CALCULATE(MAX(Schedules_Progress[Date]), ALL(CalendarData) Schedules_Progress[Item] = cal_item, Schedules_Progress[Type] = cal_type ) >= last_date, CALCULATE ( LASTNONBLANKVALUE ( CalendarData[DateID], MAX ( Schedules_Progress[Value] ) ), ALL ( CalendarData ), CalendarData[DateID] <= last_date, Schedules_Progress[Item] = cal_item, Schedules_Progress[Type] = cal_type ) ) )

(assuming Schedules_Progress[Date] is linked to CalendarData[DateID])

Re: sum the last nonblank value from multiple columns

Anonymous — Tue, 15 Apr 2025 13:48:56 GMT

sjoerdvn

so smart,
just , if you can explain it to me so i understand the logic behind it , and understand more DAX, i will appreciate it.