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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.