Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
2 years ago
Solved

LastNonBlank Value

Hi,

 

I have attached a link to my pbix file.

 

https://ufile.io/dkhq78t8

 

I have tried writing a measure that returns the last non blank value for Team A, which is 2 but I keep running into different error messages with each variation I try.

It is a simple measure but its getting the better of me - can anyone help?

Thanks

 

Need Help
Show and Tell
  • sanalytics's avatar
    sanalytics
    2 years ago

    Hello ArchStanton 

    Please find the below DAX measure for your requirement

    Team A Last Value
    CALCULATE(
         LASTNONBLANKVALUE( Sheet1[KPI],Sum(Sheet1[KPI])),
         FILTER(
               Sheet1,Sheet1[Area] = "Team A"
         ) )

    Screenshot Below

     

    Hope it will help you.

     

    Regards

    sanalytics

    If it is your solution then please like and accept it as solution

  • sanalytics's avatar
    sanalytics
    2 years ago

    Hello ArchStanton 

    The discrepancy of 36 arises due to the last non-blank value of Team C being 12, appearing three times, as expected.

    If the last non-blank value for each team member is the maximum among other quarters' KPI values, use the following DAX measure:

    lastNonBlankValue = 
    CALCULATE(
         LASTNONBLANKVALUE( Sheet1[KPI],MAXX( Sheet1,[KPI]) ),
         FILTER(
               Sheet1,Sheet1[Area] = "Team C"
         ) )
    If the last non-blank value of every team member is not the maximum, apply this alternative DAX measure
    2nd Measure =
    VAR _MaxIndex =
       CALCULATE(
        MAX( Sheet1[Index] ),
        FILTER(
            ALL( Sheet1[KPI] ),
            Sheet1[KPI] <> BLANK()
        ) )
    VAR _Result =
        CALCULATE(
             SUM( Sheet1[KPI] ),
             FILTER(
                 ALL( Sheet1[Index] ),
                 Sheet1[Index] = _MaxIndex
             ),Sheet1[Area] = "Team C" )

    RETURN
    _Result
     
    I trust this clarifies the logic. Please find the attached PBIX file also
     
    Regards
    sanalytics
    If it is your solution then please like and accept it as solution

6 Replies

  • sanalytics's avatar
    sanalytics
    Super User
    2 years ago

    Hello ArchStanton 

    Please find the below DAX measure for your requirement

    Team A Last Value
    CALCULATE(
         LASTNONBLANKVALUE( Sheet1[KPI],Sum(Sheet1[KPI])),
         FILTER(
               Sheet1,Sheet1[Area] = "Team A"
         ) )

    Screenshot Below

     

    Hope it will help you.

     

    Regards

    sanalytics

    If it is your solution then please like and accept it as solution

    • ArchStanton's avatar
      ArchStanton
      Power Participant
      2 years ago

      Hi,
      I noticed it works for Team A & B but Team C produces 36 instead of 12.

      Where is that number coming from?

      • sanalytics's avatar
        sanalytics
        Super User
        2 years ago

        Hello ArchStanton 

        The discrepancy of 36 arises due to the last non-blank value of Team C being 12, appearing three times, as expected.

        If the last non-blank value for each team member is the maximum among other quarters' KPI values, use the following DAX measure:

        lastNonBlankValue = 
        CALCULATE(
             LASTNONBLANKVALUE( Sheet1[KPI],MAXX( Sheet1,[KPI]) ),
             FILTER(
                   Sheet1,Sheet1[Area] = "Team C"
             ) )
        If the last non-blank value of every team member is not the maximum, apply this alternative DAX measure
        2nd Measure =
        VAR _MaxIndex =
           CALCULATE(
            MAX( Sheet1[Index] ),
            FILTER(
                ALL( Sheet1[KPI] ),
                Sheet1[KPI] <> BLANK()
            ) )
        VAR _Result =
            CALCULATE(
                 SUM( Sheet1[KPI] ),
                 FILTER(
                     ALL( Sheet1[Index] ),
                     Sheet1[Index] = _MaxIndex
                 ),Sheet1[Area] = "Team C" )

        RETURN
        _Result
         
        I trust this clarifies the logic. Please find the attached PBIX file also
         
        Regards
        sanalytics
        If it is your solution then please like and accept it as solution