Creating a new row with IF query in DAX

[Anonymous]

Hello,

I'm working with HR data and need to create a row to represent staff activity undertaken each by month. For example: 

Month	Name	Sick Leave in Month	Annual Leave in Month
April	Joe	1	0
May	Joe	0	2
June	Joe	1	1
May	Paul	2	0
June	Paul	1	1
June	Jon	0	0

I have a list of all staff, their start date and end date, and all the activity data with relevant dates. One of the solutions I thought might work, but doesn't, is to create an IF query along the lines of:

In Month = IF(AND([Employee Start Date]<DATE(2019,04,01), [Leavers Date]>DATE(2019,04,30)), "April 2018",
IF(AND([Employee Start Date]<DATE(2019,05,01), [Leavers Date]>DATE(2019,05,31)), "May 2018",
IF(AND([Employee Start Date]<DATE(2019,06,01), [Leavers Date]>DATE(2019,06,30)), "June 2018",
IF(AND([Employee Start Date]<DATE(2019,07,01), [Leavers Date]>DATE(2019,07,31)), "July 2018",
IF(AND([Employee Start Date]<DATE(2019,08,01), [Leavers Date]>DATE(2019,08,31)), "August 2018",
IF(AND([Employee Start Date]<DATE(2019,09,01), [Leavers Date]>DATE(2019,08,30)), "September 2018",
BLANK()))))))

However this only attribites the first relevant result (eg Joe would be April 2018, Paul would be May 2018 and Joe would by June 2018). Can anyone think of a solution? Essentially I want to write a code that creates a new line and duplicates all other information (eg name).

[HotChilli]

Can you post some sample data please and someone will help you out?

[Anonymous]

I have two sets of data, the staff data:

Employee Full Name	Employee Number	Employee Start Date	Employee End Date
Bloggs, Joe	11111	01/04/2018	01/01/4721
Bloggs, Janet	22222	15/01/2018	15/04/2018
Bloggs, Jane	33333	30/08/2015	30/02/2019
Bloggs, Jim	44444	05/02/2017	01/01/4721

And the annual leave data, NB this only reports if someone has taken annual leave, it does not include all staff:

Employee Full Name	Employee Number	Annual Leave	Month
Bloggs, Joe	11111	1	Jun-19
Bloggs, Janet	22222	1.5	Jun-19
Bloggs, Jim	44444	6	Jun-19
Bloggs, Janet	22222	6	Jul-19
Bloggs, Jane	33333	5	Jul-19
Bloggs, Jim	44444	14	Jul-19

I need to create a table like this:

Active Month	Employee Number	Employee Start Date	Employee End Date	Annual Leave
Jun-19	11111	01/04/2018	01/01/4721	1
Jun-19	22222	15/01/2018	15/04/2018	1.5
Jun-19	33333	30/08/2015	30/02/2019	0
Jun-19	44444	05/02/2017	01/01/4721	6
Jul-19	11111	01/04/2018	01/01/4721	0
Jul-19	22222	15/01/2018	15/04/2018	6
Jul-19	33333	30/08/2015	30/02/2019	5
Jul-19	44444	05/02/2017	01/01/4721	14

[HotChilli]

What happened to the sick leave data?

[Anonymous]

I've just given an example set of data. Once I know how to treat two tables I'm sure I can work out how to treat three.

[HotChilli]

You can link the tables on Employee Number in Relationship View.

Create a Measure to ensure the employees with no leave still show up

Emp leave = SUM(EmployeeLeave[Annual Leave]) + 0 

Then pull the relevant fields on to a table visualisation

[Anonymous]

Thanks but that's not what I'm looking for. I need to create a date-based table that logs all activity against the individual. The key is to have every staff member represented against every month they are active (time beween employee start date and employee end date).

[Mariusz]

Hi @Anonymous 

The below M function can be used as invoke custom function on the staff data table, this will expand the table including every month of employment, after you will  need to merge with the second table on employee number and month 

(#"Start Date" as date, optional #"End Date" as date ) => let
    enddate = if #"End Date" = null then DateTime.Date( DateTime.LocalNow() ) else  #"End Date", 
    days = Duration.Days( enddate - #"Start Date" ),
    listDates = List.Dates( #"Start Date", days, #duration( 1, 0, 0, 0 ) ),
    firstDates = List.Select( listDates, each Date.Day( _ ) = 1 ),
    monthYear = List.Transform( firstDates, each Text.Start( Date.MonthName( _ ), 3 ) & "- " & Text.End( Number.ToText( Date.Year( _ ) ), 2  ) )
    
in
    monthYear

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

That looks perfect but I've never used M before. Is there any way to do this in DAX?

[Mariusz · Accepted solution]

Hi @Anonymous 

1. In Query Editor go to Transform > New Query > New Source > Blank Query,

2. Go to advanced editor and paste the code and rename the Query1 to ExpandMonths.

3. When in your staff data table go to Add Column > General > Invoke Custom Function.
4. Make sure you set everything as on the screenshot below.

5. this will add new column "ExpandMonths", all you need to do now is click the two arrows an select expand to new rows.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Anonymous]

This worked like a charm, thanks so much! I definitely wouldn't have got there without you.

[HotChilli]

Yes, that did go through my mind but the sample data doesn't seem to fit that requirement e.g. Janet Bloggs has leave in June and July 19 but has left in April 2018

[Anonymous]

Sorry, that was a mistake. It's not easy to give a complete dataset to work with as I would need to anonymise everything.