Forum Discussion
Creating a new row with IF query in DAX
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.
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).
Community ChampionHi 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
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
That looks perfect but I've never used M before. Is there any way to do this in DAX?
- Mariusz
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. This worked like a charm, thanks so much! I definitely wouldn't have got there without you.