How to calculate days worked from a date

[NLEIGH]

Hi,

Please can someone help, I an new to Power BI and trying to build a report (in a table) to show the amount of "days worked" each of our sales team have completed.  The dataset i'm working with is as below, so in this example Goraya has 4 rows for the 17/3/2022 so I want that to show as 1, similarly with Seedat has 3 rows for 17/3/2022 so I want that to show as 1.

Many thanks for your help

[NickolajJessen · Accepted solution]

It's a good idea to get yourself a Calendar table in your data mode, when working with dates. It might not be necessary in your case, but since you are new, i'd get into the habit right away. Second you'll want to connect your Calender Table to your Data table. Right now you have a column called "Call Date", which is actually a date time. I'd rename it appropiately. Create a column that is actually Call Date (meaning to hh:mm:ss) and connect it to your calendar table.
From there you can just do a count on the amount of unique Dates. 

[NLEIGH]

Hi, Many thanks for your quick reply, I made a couple of changes and then did count distinct and it worked like a treat !!  I just have one other question now, the total of the count distinct is now correct I really want it to sum up the colu

mn, do you know if there is a way round this ?

Thanks,

[NickolajJessen]

Do you mean  19 + 19 + 20 + 15 .... + 8 = 139 at the total?

[Avantika-Thakur]

Hi @NLEIGH ,

You can achieve this by performing some transformations in Power Query Editor-
1) You can split the Date time column into separate Date and Time columns and remove the time part of it if not required

2) And then you can remove duplicates from the table

Hope this helps!

Thanks,

Avantika Thakur