Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Andre-SiS
Frequent Visitor

Distinct count per day

I have a table that shows sales data (SalesDashboard). 

I want to count the number of sales per day. 

I want to do this by count distinct values per day - since each sale has a distinct order number. 

 

I've gotten this far, but how can I count it per day instead of the overall sum?

 

Number of Sales Per Day =
CALCULATE (
DISTINCTCOUNT(SalesDashboard[Entry_No],
))​

 

In my data model, I have a seperate DateTable so that all days are existing. 

In my table SalesDashboard, I also have a date field that's related to the DateTable. 

 

I have looked at other posts, but I can't seem to get it to work. 

1 ACCEPTED SOLUTION
miTutorials
Super User
Super User

Please try the below measure.

 

DistinctCountByDate = 
CALCULATE(
    SUMX(
        VALUES('YourDataTable'[Date]),
        CALCULATE(
            DISTINCTCOUNT('YourDataTable'[Order ID])
        )
    )
)

 

Regards

Ismail

 

View solution in original post

2 REPLIES 2
Andre-SiS
Frequent Visitor

Works like a charm - thank you!

miTutorials
Super User
Super User

Please try the below measure.

 

DistinctCountByDate = 
CALCULATE(
    SUMX(
        VALUES('YourDataTable'[Date]),
        CALCULATE(
            DISTINCTCOUNT('YourDataTable'[Order ID])
        )
    )
)

 

Regards

Ismail

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors