cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Impactful Individual

## Only count/sum Quantity of last log date

So I have this table output:

So I have a delivery with multiple materials.

But some of these materials (see for example the yellow) have 2 log dates.

The correct Quantity for  this material would be 2 and not 4 (when I would take out the date)

QCorr = CALCULATE([Quantity],Test[LogDate] = Test[MaxLog])
Actually expected this would work. No clue why it still shows 2 where logdate is 18 and maxlog is 19.

But this is not doing what I want.
In the end I want to take the dates out and that material should show 2.
Because the total should be 102 not 122.
122 is because all of the doubles in this delivery.

How can I make it

1 ACCEPTED SOLUTION
Super User

Opsss... I copied badly the measure:

``QCorr = SUMX(VALUES('Table'[Material]),[sumSc])``

6 REPLIES 6
Impactful Individual

@mlsx4
unfortunately not:

ps: maxlog is a calculated column

Super User

Hi @rpinxt

I have created an auxiliary sum to get the maximum value in case of several rows:

``sumSc = CALCULATE(MAX('Table'[Quantity]), FILTER(ALLEXCEPT('Table','Table'[Material]), 'Table'[LogDate]=MAX('Table'[LogDate])))``

And now, in QCorr:

``QCorr = SUMX(VALUES('Table'[Material]),IF(DISTINCTCOUNT('Table'[Material])>1,[sumSc],SUM('Table'[Quantity])))``

You don't need any "MaxLog" column

Impactful Individual

Thanks @mlsx4 but look on the second row....it says 4 and should be 2.

Yes your total of 102 is correct but if you sum all the line separate you will again get 122.

Super User

Opsss... I copied badly the measure:

``QCorr = SUMX(VALUES('Table'[Material]),[sumSc])``

Impactful Individual

Yes thanks @mlsx4 this seems to work.
But you need to remove the date colunns.

This is part of a bigger thing.

I have more countries, deliveries and months etc.

I should all go into a stacked column chart.

With periods on the x-axis where I link a date field (maxlog) to a date table (auto).

Wondering if it will give the correct output.

But will test. Thanks for this!

Super User

I think you shouldn't include those columns in the table or they will be added to the sum. If you just leave delivery, material and qcorr, it should work.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors