Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
antoniopgouveia
Frequent Visitor

Calculate value by last date and grouped by ID

Hi all,

 

I have a table that records all the changes made to a list of opportunities. These changes can affect the same opportunity at different time points. For example, if an opportunity is created and a certain sales amount is input and later changed to a new value, both modifications would be listed in the table. I have the following sample:

 

ParentId                                                                               CreatedDate                                                   Amount__hst     

006D000000pOj4XIAS03/04/2018 09:4699257
006D000000rHnhtIAC03/04/2018 23:57116110
006D000000uHe3xIAC03/04/2018 21:10115000
006D000000xjhG7IAI03/04/2018 11:5080000
006D000000yLtscIAC02/04/2018 20:11110000
006D000000yLtscIAC02/04/2018 20:56113000
006D000000yLtscIAC02/04/2018 20:56113000
006D000000yLvrnIAC03/04/2018 09:381
006D000000yLvrnIAC03/04/2018 09:39117300
006D000000yLy1uIAC03/04/2018 22:30 
006D000000yLy1uIAC03/04/2018 22:34115000
006D000000yLy1uIAC03/04/2018 22:54115000
006D000000yLy3MIAS03/04/2018 22:38100000
006D000000yLy3MIAS03/04/2018 22:40170100
006D000000yLy3MIAS03/04/2018 22:43175300
006D000000yLy3MIAS03/04/2018 22:48184300
006D000000yLy3MIAS03/04/2018 22:49184525
006D000000yLy3MIAS03/04/2018 22:50184615
006D000000yLy3MIAS03/04/2018 22:52184770

 

As you can see some of the changes in amount affect the same opportunity (same ID) and I would like to be able to have a measure that would find the last inserted value (by creation date) and by opportunity ID. The goal is to track changes on opportunities so when I sum all the last values for each opportunity, I can compare it for different time intervals. 

 

I have come up with the following measure that returns the last input value:

 

LastAmount = CALCULATE(LASTNONBLANK('Table'[Amount__hst], ""), FILTER('Table', MAX('Table'[CreatedDate])))

However, it does not sum the different last values for the different opportunities. Any help?

 

For this example the value I would be looking for would be: 99257 + 116110 + 115000 + 80000 + 113000 + 117300 + 115000 + 184770

 

Best regards,

Antonio

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@antoniopgouveia

 

Antonio,

 

Try adding this MEASURE

 

Measure =
IF (
    HASONEFILTER ( 'Table'[ParentId ] ),
    [LastAmount],
    SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

@antoniopgouveia

 

Antonio,

 

Try adding this MEASURE

 

Measure =
IF (
    HASONEFILTER ( 'Table'[ParentId ] ),
    [LastAmount],
    SUMX ( ALLSELECTED ( 'Table'[ParentId ] ), [LastAmount] )
)

Regards
Zubair

Please try my custom visuals

Thanks! That works perfectly!

 

Is there any chance of getting the same result with one measure only? I have tried to replace the LastAmount by the actual expression in the formula you have posted but the result I'm getting is different. 

@antoniopgouveia

 

See attached file as well

 

antonio.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.