Getting the value on Last occurrence in table

DAX DAX Calculations
1

Hi,
Looking for your help with following.

I have Following table that has Date, Account ID, PLIs

There will be a date slicer in the report .The output that I am trying to achieve is mentioned in the "Expected Result Column, Which is basically the PLIs value, on the date when Account ID appeared in the table. According to date range selected using the date slicer.

This Table has been sorted by Account ID

Date Account ID ISL Expected Result
08.03.2021 1000001 0-250
08.07.2021 1000001 251-510 251-510
08.04.2021 1000002 0-250
08.16.2021 1000002 0-250 0-250
08.05.2021 1000003 551-750 551-750
08.06.2021 1000004 751-1000
08.08.2021 1000004 751-1000 751-1000
08.09.2021 1000007 0-250 0-250
08.10.2021 1000008 551-750 551-750
08.11.2021 1000009 0-250 0-250
08.12.2021 10000010 0-250 0-250
08.13.2021 10000011 551-750
08.19.2021 10000011 551-750
08.20.2021 10000011 251-510 251-510
08.14.2021 10000012 751-1000 751-1000
08.15.2021 10000013 0-250
08.18.2021 10000013 0-250 0-250
08.17.2021 10000015 251-510 251-510

When sorting on Date Column : this will be the expected table:

Date Account ID ISL Expected Result
08.03.2021 1000001 0-250
08.04.2021 1000002 0-250
08.05.2021 1000003 551-750 551-750
08.06.2021 1000004 751-1000
08.07.2021 1000001 251-510 251-510
08.08.2021 1000004 751-1000 751-1000
08.09.2021 1000007 0-250 0-250
08.10.2021 1000008 551-750 551-750
08.11.2021 1000009 0-250 0-250
08.12.2021 10000010 0-250 0-250
08.13.2021 10000011 551-750
08.14.2021 10000012 751-1000 751-1000
08.15.2021 10000013 0-250
08.16.2021 1000002 0-250 0-250
08.17.2021 10000015 251-510 251-510
08.18.2021 10000013 0-250 0-250
08.19.2021 10000011 551-750
08.20.2021 10000011 251-510 251-510
2

@jps What is the logic behind getting 251-510 here: ?

image

3

Hi, Sorry that was a typo. it made no sense. :slight_smile: - I have just edited my post again.

4

Hello @jps,

Thank You for posting your query onto the Forum.

Below is the measure provided for the reference alongwith with the screenshot of the final results -

Last Occurence Value = 
VAR _Current_Account_ID = 
SELECTEDVALUE( Data[Account ID] )

VAR _Max_Date = 
MAXX(
    FILTER( ALL( Data ) , 
        Data[Account ID] = _Current_Account_ID ) ,
    Data[Date] )

VAR _Last_Occurence_Value = 
CALCULATE( LASTNONBLANKVALUE( Data[Date] , 
    MAX( Data[ISL] ) ) ,
        ALLEXCEPT( Data , Data[Account ID] ) )

VAR _Results = 
IF( SELECTEDVALUE( Data[Date] ) = _Max_Date , 
    _Last_Occurence_Value , 
    " " )

RETURN
_Results

Final Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Last Occurence Value - Harsh.pbix (18.9 KB)

4 Likes
5

That was Spot On! Appreciate your response.

Thank you. :+1:

6

Hello @jps,

You’re Welcome. :slightly_smiling_face:

I’m glad that we were able to assist you.

Thanks and Warm Regards,
Harsh

1 Like