Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Bearcat22
New Member

Calculating total unique days with multiple entries and date range overlap

I am having trouble with a utilization report I am trying to build in calculating the total number of unique days an Object was in service. My table contains a start and an end date for each ObjectCode but an object can have two entries in a single day, or have an overlap in the date range for example :

Bearcat22_0-1696255978050.png

7/27 has two entries and therefore would give me an extra day if I were to sum this column by the selected month. How can I find a count for only the unique days in the selected month?

1 REPLY 1
DataZoe
Employee
Employee

@Bearcat22 An apporach would be to expand out your dates, so instead of the start and end of the range, show by each day in that range, called say InServiceDate. Now you can count rows per InServiceDate. Optionally you could add a Date table to your model. Join The Date table to the InServiceDate. 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.