Calculate Totals of SUM MAX Date

[Laho]

Hi All,

I've been struggeling with the totals of SUM MAX Date measure. My Measure looks like this right now. Works as expected in row;

LastActiveUsers = CALCULATE(
SUM('Daily licensing'[ActiveUsers]),
FILTER( 'Daily licensing', 'Daily licensing'[ReceivedOn] = MAX('Daily licensing'[ReceivedOn])))

We're receiving several records of the same customer on a day. Thats why I only want the MAX value on that day.

I know there are a lot of articles based on fixing totals within tables. But can't fix this one somehow.

The thing is that the totals needs to be working as well. Table looks as follow;

ReceivedOn	Customer	ActiveUsers	LastActiveUsers
08-10-2020 15:01:30	A	123	123
08-10-2020 18:01:30	B	1	1
08-10-2020 18:02:35	B	21	21
08-10-2020 09:01:20	C	5	5
total		150	1

Any help would be apreciated.

[amitchandak]

@Laho , try like

LastActiveUsers = CALCULATE(
SUM('Daily licensing'[ActiveUsers]),
FILTER( allselcted('Daily licensing'), 'Daily licensing'[ReceivedOn] = MAX('Daily licensing'[ReceivedOn])))

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Laho]

Thanks,

LastActiveUsers = CALCULATE(
SUM('Daily licensing'[ActiveUsers]),
FILTER( allselcted('Daily licensing'), 'Daily licensing'[ReceivedOn] = MAX('Daily licensing'[ReceivedOn])))

This returns the max value, but inline it returns the max value overall 😉 so 123 in each row

[v-deddai1-msft]

Hi @Laho ,

Would you please try the following measure:

LastActiveUsers = CALCULATE(MAX('Table'[ActiveUsers]),FILTER(ALL('Table'),'Table'[Customer] in DISTINCT('Table'[Customer])))

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

Best Regards,

Dedmon Dai

[Laho]

Hi Dedmo,

Thanks for your reply. 

LastActiveUsers = CALCULATE(MAX('Table'[ActiveUsers]),FILTER(ALL('Table'),'Table'[Customer] in DISTINCT('Table'[Customer])))

This is returning the MAX value for the table. So in youre screenshot 123. But what i need is that i need the MAX of customer on day x. So right now my info is delivered this way:

ReceivedOn	Customer	ActiveUsers
08-10-2020 15:01:30	A	123
08-10-2020 18:01:30	B	1
08-10-2020 18:02:35	B	21
08-10-2020 09:01:20	C	5
total		150

I want to create a calculation that only the max over customer is picked of date x. So in this case this records should be included:

ReceivedOn	Customer	LastActiveUsers
08-10-2020 15:01:30	A	123
08-10-2020 18:02:35	B	21
08-10-2020 09:01:20	C	5
total		149

The current calculations all do work inline but are returning icorrect totals:

LastActiveUsers = CALCULATE(
SUM('Daily licensing'[ActiveUsers]),
FILTER( 'Daily licensing', 'Daily licensing'[ReceivedOn] = MAX('Daily licensing'[ReceivedOn])))

CALCULATE(
MAX('Daily licensing'[ActiveUsers]),
All(Daily Licensing),Values('Daily licensing'[ActiveUsers])

[v-deddai1-msft · Accepted solution]

Hi @Laho ,

Please refer to the following measure:

LastActiveUsers = SUMX(SUMMARIZE('Table','Table'[ReceivedOn].[Date],'Table'[Customer],"maxauser",MAX('Table'[ActiveUsers])),[maxauser])

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

Best Regards,

Dedmon Dai

[Laho]

Hi Dedmon,

It looks like this is the solution! Thanks a lot. 
Can you clairify the solution for me? 

maxauser

Laurens, 

[v-deddai1-msft]

Hi @Laho ，

I create a summary table and get the maximum ActiveUsers for each customer.

Best Regards,

Dedmon Dai

[VijayP]

LastActiveUsers = CALCULATE(
MAX('Daily licensing'[ActiveUsers]),
All(Daily Licensing),Values('Daily licensing'[ActiveUsers])

  @Laho Trythis  and let me know  and share your Kudoes

[Laho]

Thanks,

CALCULATE(
MAX('Daily licensing'[ActiveUsers]),
All(Daily Licensing),Values('Daily licensing'[ActiveUsers])

This wil get the MAX value from the table. So 123 in this case.