Forum Discussion
Getting Monthly Data from effective day
Dear all,
I have a though task at hand.
I have a table like this:
| Employee ID | Job Information Date | Department |
| 1 | 01/01/2019 | Alfa |
| 1 | 15/01/2019 | Beta |
| 1 | 28/02/2019 | Gamma |
That means that "Employee 1" was in "Alfa" team btw January 1st and January 31st. On the 31st of January, he moved to "Beta" team and stayed there until 28th of Feb, then on the 28th of Feb, he moved to Gamma team. Since there's no change in his job information since then, he's still in the Gamma team as we understand. Let's say today it's 31st of March, so we know that Employee 1 is still working in the Gamma team
Based on this, I'd like to create a table like this where I can see the number of days per month spent in a department:
| Employee ID | Year | Month | Number of days | Department |
| 1 | 2019 | January | 15 | Alfa |
| 1 | 2019 | January | 16 | Beta |
| 1 | 2019 | February | 28 | Gamma |
| 1 | 2019 | March | 31 | Gamma |
Do you think that it's managable (preferably within M language)?
Thank you for your help in advance.
Regards,
Ugur
1 Reply
Hi,
I assume you would be able to Create an Index Comn in your Table. This Column would have the list of numbers in the Increasing Sequence.
Now use the Following Measure
ADDCOLUMNS(SUMMARIZECOLUMNS(Employee_ID,"Year", YEAR(Date),"Month",MONTH(Date)),"Difference in Days",
VAR Next_Index=Table[Index]+1
VAR Index=Table[Index]
RETURN CALCULATE(DATE,FILTER(TABLE,Table[Index]=Index))- CALCULATE(DATE,FILTER(TABLE,Table[Index]=Next_Index))))
You could also use the DateDiff function but I dont think it would accept Expressions as a part of the Date Arguments.
RETURN DATEDIFF