sum the last nonblank value from multiple columns

[Abdullah_Dax]

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 

[sjoerdvn]

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

[Abdullah_Dax]

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

[sjoerdvn · Accepted solution]

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

[Abdullah_Dax]

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

[Abdullah_Dax]

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

[bhanu_gautam]

@Abdullah_Dax 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]