DAX Formular count days over month between two days

[rast286]

Hello everybody,

I have following problem and hope anybody could help me out.

I have to count the days of businesstrips of our employees over the month.

A businesstrip always has a startdate and an enddate. If no enddate is available the businesstrip is still going.

We want to see how many days our employees were on bussinestrip in January, February, March, etc... 

For me, the difficult thing is, when a businesstrip went over two or more month.

For example:

Trip Nr. 1 Startdate 27.01.2023, Enddate 04.03.2023.

That are 5 days in January, 28 days in February and 4 days in March.

Trip Nr. 2 Startdate 25.02.2023, Trip still ongoing

That are 4 days in February and 14 days in march (because today is March the 14.th)

Hope the infos are enough and someone could help me.

Thanks in advance!

Ralph

[Ahmedx · Accepted solution]

1) You need to create a calendar table

2) write measure like this:

3)Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhjvgpTWw9G7Cx3jl?e=X6i7Qh

[Ahmedx · Accepted solution]

1) You need to create a calendar table

2) write measure like this:

3)Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26RhjvgpTWw9G7Cx3jl?e=X6i7Qh

[rast286]

One more Question...
is it also possible to get the totals in the respective month?
For example like this?

I have tried it by my own but I am pretty new in writing DAX measures.

Regards

Ralph

[Ahmedx · Accepted solution]

[rast286]

Thanks for your fast reply.
Worked great.

Thanks a lot!!

Regards

[rast286]

Hi Ahmedx,

awesome! Just tried it and it worked great.

Thanks a lot!

Best wishes 

Ralph

[Ahmedx]

or maybe you just want a calculated column

[rast286]

No the first one would be the right solution.

The one with the month.
Would be nice to know how you did that? 

[Ahmedx]

i already answered

[Ahmedx]

Is this what you are looking for?

[rast286]

Exactly!

[BITomS]

Hi @rast286,

My suggestion would be to generate a row for each day between the start and end dates by unpivoting the data. Then you can perform a simple count on the number of rows (which can then be sliced by month, employee etc.).

This article could be useful for your purposes: https://natechamberlain.com/2018/08/08/how-to-add-rows-for-dates-between-start-and-end-dates-in-powe...

Hope that helps.