Distinct count from another table on max date

[Sibrulotte]

Hi, 

two tables: 

Balances:

Date	Account	Amount
2024-05-27	1	10
2024-05-27	2	10
2024-05-27	3	15
2024-05-27	4	20
2024-05-27	6	50
2024-04-30	1	5
2024-04-30	2	5
2024-04-30	5	40
2024-04-30	3	10

Acounts:

Account	Client
1	A
2	B
3	C
4	A
5	D
6	E

A client can have more than one account, pretty basic.

When I try to do a distinct count of clients for the max date (in this case may 27th) I get the 

nb_eparg_solde_max =

// define the max date

var max_Balances = filter(all(Balances), Balances[Date] = max(Balances[Date]))

return

calculate(distinctcount(Accounts[Clients]), max_solde)

I should be getting 4 clients but I get all 5 of them...

[rajendraongole1 · Accepted solution]

Hi @Sibrulotte - You need to follow a structured approach to ensure that you're considering only the clients that have transactions on the maximum date without duplication

Use the below measure

DistinctClientCountOnMaxDate = 

VAR MaxDateValue = [MaxDate]

RETURN

CALCULATE(

    DISTINCTCOUNT(Table2[Client]),

    FILTER(

        Table1,

        Table1[Date] = MaxDateValue

    )

)

 output:

can you please use the above logic and let know.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship (many to One and Sigle) from the Date column of the Balances table to the Date column of the Calendar Table.  In the Balances tables, write this calculated column formula to bring over the Client from the Accounts table

Client = related(Accounts[Client])

Write this measure

Measure = calculate(distinctcount(Balances[Client]),datesbetween(calendar[date],max(calendar[date]),max(calendar[date])))

Drag this measure to a card visual.

Hope this helps.

[vajifdardevdatt]

Hi

Please help me with how I prepared for the Microsoft Power BI exam.

[ThxAlot · Accepted solution]

[rajendraongole1 · Accepted solution]

Hi @Sibrulotte - You need to follow a structured approach to ensure that you're considering only the clients that have transactions on the maximum date without duplication

Use the below measure

DistinctClientCountOnMaxDate = 

VAR MaxDateValue = [MaxDate]

RETURN

CALCULATE(

    DISTINCTCOUNT(Table2[Client]),

    FILTER(

        Table1,

        Table1[Date] = MaxDateValue

    )

)

 output:

can you please use the above logic and let know.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[vajifdardevdatt]

Please can  someone help me with what I can buy to practice for the Microsoft  Power BI exam   after two weeks I have an exam