Count between from and to date

[Anonymous]

Hi, 

I need to show in a table/matrix the days someone is gone based on from and to date:
Dataset includes datetable.

The dataset looks somewhat like this (3 columns):
Person    from               to
1             03.02.2020     07.02.2020
2             04.02.2020      07.02.2020

Desired matrix:
Weeknr 6  
day                Mon   Tue    Wed    Thu   Fri    Sat  Sun
person 1        1          1        1         1        1           
person 2                    1         1         1        1        

[Mariusz]

Hi @Anonymous 

Please see the attached file with a solution in Power Query.

Table - you sample 

Table ( referenced ) - Table referenced to Table and Transformed to expand dates ( Added Colum step )

Calendar - list of dates + weekday column ( date dimension )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

@Mariusz, nice solution with power query.

See dax solution by Greg_Dekler.

You want a modification of Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[Greg_Deckler · Accepted solution]

You want a modification of Open Tickets. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[amitchandak]

Refer this blog can help to deal with between date. Now instead of count of employee take sum of weekday

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