Forum Discussion
DAX - month-based bar chart for data with date range
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 |
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-trend/ba-p/882970
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 LinkedinHi 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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
https://community.powerbi.com/t5/Desktop/Columns-to-Rows/td-p/136649
Here is a sample on how to do this
daxCommunity Support
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- amitchandak
Super User
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-trend/ba-p/882970
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