Break months between dates as Columns

[Anithalakshmi]

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

[Greg_Deckler · Accepted solution]

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.

[Anithalakshmi]

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