Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

sum the last nonblank value from multiple columns

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's avatar
    sjoerdvn
    1 year ago

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

6 Replies

  • sjoerdvn's avatar
    sjoerdvn
    Icon for Solution Sage rankSolution Sage
    1 year ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        sjoerdvn
        Icon for Solution Sage rankSolution Sage
        1 year ago

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

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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]