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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Dn_wemn
Helper I
Helper I

Sum Value by latest date and ID field

Hi

How can I sum the Sales value by ID field and by latest Date of each ID?

Dn_wemn_0-1708982853487.png

The expected result of new measure is: 4567 (B) + 358 (A) + 453 (A) = 5378

 

Thank you

 

SalesDeliverdateID
23318.01.2023A
56318.01.2023A
45301.02.2023A
35801.02.2023A
43501.04.2023B
76804.01.2024B
54711.01.2024B
34611.02.2024B
456711.04.2024B
1 ACCEPTED SOLUTION
v-yohua-msft
Community Support
Community Support

Hi, @Dn_wemn 

 

You can try the DAX expression below, I start by creating a measure to calculate the most recent date:

 

LatestDatePerID = 
CALCULATE(
    MAX('Table 1'[Deliverdate]),
    ALLEXCEPT('Table 1', 'Table 1'[ID])
)

 

vyohuamsft_0-1709022160533.png

 

Then create a new measure to calculate the sum:

 

SumSalesByLatestDateAndID = 
SUMX(
    FILTER(
        'Table 1',
        'Table 1'[Deliverdate] = 'Table 1'[LatestDatePerID]
    ),
    'Table 1'[Sales])

 

Here is my preview:

 

vyohuamsft_1-1709022296036.png

 

 

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-yohua-msft
Community Support
Community Support

Hi, @Dn_wemn 

 

You can try the DAX expression below, I start by creating a measure to calculate the most recent date:

 

LatestDatePerID = 
CALCULATE(
    MAX('Table 1'[Deliverdate]),
    ALLEXCEPT('Table 1', 'Table 1'[ID])
)

 

vyohuamsft_0-1709022160533.png

 

Then create a new measure to calculate the sum:

 

SumSalesByLatestDateAndID = 
SUMX(
    FILTER(
        'Table 1',
        'Table 1'[Deliverdate] = 'Table 1'[LatestDatePerID]
    ),
    'Table 1'[Sales])

 

Here is my preview:

 

vyohuamsft_1-1709022296036.png

 

 

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you, this has worked.

I tried to do this in one measure, so your first measure was VAR and then I used RETURN to enter your second measure. But this has not worked. Why does it only work in 2 measures? Thx.

Ashish_Mathur
Super User
Super User

Hi,

The latest date of ID A is 01/02/2023.  Why are you including 233 in the answer?  Shouldn't the answer be 4567+358+453?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, you're right, I corrected this. Thanks

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1709034795047.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for your suggestion, this looks good as well.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Share data in a format that can be pasted in an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

SalesDeliverdateID
23318.01.2023A
56318.01.2023A
45301.02.2023A
35801.02.2023A
43501.04.2023B
76804.01.2024B
54711.01.2024B
34611.02.2024B
456711.04.2024B

 

I dont know how to upload the pbix file though...

selimovd
Super User
Super User

Hey @Dn_wemn ,

 

if you put it in the table, the measure should look like this:

Sales of MaxDate =
VAR _MaxDeliveryDate = CALCULATE ( MAX ( myTable[Deliverdate] ), ALLEXCEPT ( myTable, myTable[ID] ) )
VAR _Sales = CALCULATE ( SUM ( myTable[Sales] ), myTable[Deliverdate] = _MaxDeliveryDate )
RETURN
    _Sales

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
 

Hi, thanks, your solution takes the correct max dates and ID, but the total is wrong.

Dn_wemn_0-1709019875023.png

 

I tried with SUMX but it has not worked either.

Dn_wemn_1-1709019931595.png

Do you know how to get the correct total which is 5378?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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