Multiple dates

[Syndicate_Admin]

Good afternoon

I'm new and I have a problem that I can't solve:

I have a table of facility data, in which I have the start, end and service days dates for each installation (assemblies). I want to count the services per day, but since the duration of the installation is >1 day, it no longer tells me that the next day that installer is busy, because it counts only the dates that appear in the table.

Something like that

Service	start date	days of service	end date
A	29/03/2021	2	30/03/2021
B	29/03/2021	3	31/03/2021
C	30/03/2021	3	01/04/2021
D	30/03/2021	2	31/03/2021
E	30/03/2021	4	02/04/2021
F	30/03/2021	1	30/03/2021
G	31/03/2021	2	01/04/2021
H	01/04/2021	2	02/04/2021
I	02/04/2021	3	04/04/2021
J	03/04/2021	1	03/04/2021

On the 31st/03rd I would have to show up that I have 5 installations in progress, but only counts 1, for its start date.

In excel is very simple, I make a macro that inserts me an extra row with the same data but changing the service date and ready, but the data automatically reaches me power bi and I didn't want to have to go through excel.

Could someone help me?

A greeting and thank you very much in advance

[v-deddai1-msft · Accepted solution]

Hi @Syndicate_Admin ,

You can create a dim_date table for slicer:

Dim_Date = CALENDARAUTO()

Then use the following  measure to  show up installations in progress:

service in progress = CALCULATE(COUNT('Table'[Service]),FILTER('Table','Table'[start date]<=SELECTEDVALUE(Dim_Date[Date])&&'Table'[end date]>=SELECTEDVALUE(Dim_Date[Date])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Syndicate_Admin]

Thanks a lot! This is extremely what I needed!

It's perfect!

[amitchandak]

@Syndicate_Admin , Check if my HR blog on similar topics of two dates can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...