Forum Discussion
Solved
Break months between dates as Columns
The data I have is
| EmpID | Name | Project | Start Date | End Date | Effort% |
| 1 | Name1 | P1 | 01-02-2018 | 29-05-2018 | 100 |
| 2 | Name5 | P2 | 01-01-2018 | 01-04-2018 | 60 |
| 3 | Name2 | P3 | 01-04-2018 | 01-05-2018 | 70 |
| 4 | Name2 | P4 | 01-01-2018 | 03-04-2018 | 30 |
| 5 | Name5 | P5 | 03-01-2018 | 04-08-2018 | 40 |
PowerBI dashboard.
I Need the output breakup into Months, with conditional formatting in color code based on effort %.
| EmpID | Name | Project | Start Date | End Date | Effort% | Jan | Feb | Mar | Apr | May | Jun | Jul | Till Dec |
| 1 | Name1 | P1 | 01-02-2018 | 29-05-2018 | 100 | 100 | 100 | 100 | 100 | ||||
| 2 | Name5 | P2 | 01-01-2018 | 01-04-2018 | 60 | 60 | 60 | 60 | 60 | ||||
| 3 | Name2 | P3 | 01-04-2018 | 01-05-2018 | 70 | 70 | 70 | ||||||
| 4 | Name2 | P4 | 01-01-2018 | 03-04-2018 | 30 | 30 | 30 | 30 | 30 | ||||
| 5 | Name5 | P5 | 03-01-2018 | 04-08-2018 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 |
Not sure if you want to do this in M or DAX. In DAX you can create calculated columns with the following formula:
Jan = VAR MyMonth = 1
RETURN IF(MONTH([Start Date])<=MyMonth && MONTH([End Date])>=MyMonth,[Effort%],BLANK())Just change the column name and the MyMonth value for each column.
2 Replies
Not sure if you want to do this in M or DAX. In DAX you can create calculated columns with the following formula:
Jan = VAR MyMonth = 1
RETURN IF(MONTH([Start Date])<=MyMonth && MONTH([End Date])>=MyMonth,[Effort%],BLANK())Just change the column name and the MyMonth value for each column.
my startdate is in Dec 2017 and enddate is in Mar2018
The date comparision fails for dec where the month is 12.Should I use datediff function? Not sure how
