DAX for Distinct Account #'s per day for a sliced month

[mclawler]

The above image shows the Measures I am trying to generate, but the Average tiles are not correct.

When a given month is chosen within the slicer, Total Distinct Accounts is correctly generating how many Distinct Account Numbers are being utilized over the course of the entire selected month.  Even though, each distinct account is being accessed multiple different times on multiple different days.

What I need measures for are the Daily Sum of Distinct Account Numbers utilized, per day. 

Example :

Day 1 = March 1, 2025 = 80 Distinct Account Numbers

Day 2 = March 2, 2025 = 57 Distinct Account Numbers

Day 3 = March 3, 2025 = 63 Distinct Account Numbers

Etc.

In order to get the Average per day with something like = DIVIDE(SUM(Day1-Day30),Workdays)

In order to get the Average per hour with something like = DIVIDE(SUM(Day1-Day30),WorkHour)

I've tried a couple measres on my own and they aren't givingme what I'm looking for, not even close. 

Tested:

DistinctAccountPerDay =

SUMX(

    VALUES('Activities Master Workbook'[CreatedDate].[Day]),

    DISTINCTCOUNT('Activities Master Workbook'[Account Number]

)

)

Tested: 

Average_Accounts_Per_Workday =

VAR DaysWorked = DISTINCTCOUNT('Activities Master Workbook'[Created Date].[Date])

VAR TotalMembers = SUMX(VALUES('Activities Master Workbook'[Created Date].[Date]), DISTINCTCOUNT('Activities Master Workbook'[Account Number]))

RETURN

DIVIDE(TotalMembers, DaysWorked, 0)

Thank you so much! 

[Ashish_Mathur · Accepted solution]

Hi,

You should create a Calendar table.  Build a relationship between the Date column of your Fact table with the Date column of the Calendar Table.  To your visuals/filters/slicers, drag Date from the Calendar table.  Write these measures

Measure = DISTINCTCOUNT('Activities Master Workbook'[Account Number])

Measure1 = SUMX(Calendar,[Measure])

Hope this helps.

[mclawler]

That did the trick!!! I had to modify my DAX just a little bit but it works great thank you!

SUMX(VALUES('Activities Master Workbook'[CreatedDate].[Date]),[AccountNumberDistinctCount])

I had also figured out a workaround by using Concatenate and merging the Date Column with the Account Number column and then doing a DistinctCount of that per the slicer.  For anyone else that may come across this thread in the future, gave me the exact same results as above.  

Column 1 = CONCATENATE('Activities Master Workbook'[CreatedDate].[Date], " " & 'Activities Master Workbook'[Account Number])

Measure 1 = DISTINCTCOUNT('Activities Master Workbook'[Merged Day&AccountNumber])

Thanks again!! 

[Ashish_Mathur]

You are welcome.