LastNonBlank Value

[ArchStanton]

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

[sanalytics · Accepted solution]

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]

Perfect, thanks a lot!

[sanalytics · Accepted solution]

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]

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

Where is that number coming from?

[sanalytics · Accepted solution]

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

https://filebin.net/19ecam6sviztll5y

Regards

sanalytics

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

[ArchStanton]

I think I've worked out why this isn't working in my Live data model, its nothing to do with the measures you created, its to do with the data itself. 

Thank you once again for your help!

[ArchStanton]

Thanks, that works in both our test pbix files.

However, when I try to apply that to my live data model (which I cannot share), the result = BLANK for every Team I select?

Any ideas why would that be?

ERT Measure = 
VAR MaxIndex =
       CALCULATE(
            MAX('Customer Wait (2)'[Index]),
            FILTER
                (All('Customer Wait (2)'[KPI]),
                    'Customer Wait (2)'[KPI] <> BLANK()
                )
)
        VAR Result = 
        CALCULATE(SUM('Customer Wait (2)'[KPI]),
            FILTER(
                ALL('Customer Wait (2)'[Index]),
                 'Customer Wait (2)'[Index]= MaxIndex),
                  'Customer Wait (2)'[Area] = "Early Resolution")
        RETURN
            Result