DAX - month-based bar chart for data with date range

[Anonymous]

Hi Experts,

I want to plot the count of the number "working days"(like networkdays) of leaves taken by employee per month.

But the data is in date-range, can you please guide me on how can i calculate the number of days in each date range with in a month.

Sample data and expected result is attached.

Reagrds

DIvya Joy

ID Number	Full Name	Leave Type Descripion	Leave Start Date	Leave End Date	Date Paid	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19
1234	David	Sick	7/09/2018	14/09/2018	3/10/2018	0	0	0	0	0	0	0	0	7	0	0	0	0	0	0	0	0	0	0
1234	David	Sick	27/09/2018	27/09/2018	17/10/2018	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
1234	David	Annual	5/11/2018	5/11/2018	14/11/2018
1234	David	Sick	7/11/2018	7/11/2018	28/11/2018
1234	David	Sick	9/11/2018	9/11/2018	28/11/2018
1234	David	Annual	27/12/2018	9/01/2019	9/01/2019
1234	David	Annual	10/01/2019	10/01/2019	23/01/2019
1234	David	Annual	11/01/2019	14/01/2019	23/01/2019
1234	David	Sick	29/01/2019	29/01/2019	6/02/2019

[Anonymous · Accepted solution]

A date table will do what you need.  You can simple count the rows inside the date table.  If you need to exclude weekends from the count, you can use the functions in both Power Query or DAX to flag rows as being on the weekend, then simply exclude them from the count.

[amitchandak · Accepted solution]

Please try the following. The assumption is that you have a date table and that is joined with paid date

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

leaves= CALCULATE(COUNTx(FILTER(Employee,Employee[Leave Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) 
|| Employee[Leave End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Date Paid],'Date'[Date],None))	

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[dax · Accepted solution]

Hi @Anonymous ,

You need to transform data structure and create calendar table to achieve this goal. You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[Anonymous]

This is amazing, just written using 2 measures..  

[amitchandak · Accepted solution]

Please try the following. The assumption is that you have a date table and that is joined with paid date

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

leaves= CALCULATE(COUNTx(FILTER(Employee,Employee[Leave Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) 
|| Employee[Leave End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Date Paid],'Date'[Date],None))	

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous · Accepted solution]

A date table will do what you need.  You can simple count the rows inside the date table.  If you need to exclude weekends from the count, you can use the functions in both Power Query or DAX to flag rows as being on the weekend, then simply exclude them from the count.

[Anonymous]

https://community.powerbi.com/t5/Desktop/Columns-to-Rows/td-p/136649

Here is a sample on how to do this