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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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