Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have something i can't seem to figure out.
I have a dates table with contiguous dates and i need to create a custom column that returns pay periods.
My requirements are:
1. if the month has 4 mondays, then i want to return the 2nd and 4th monday date.
2. if the mont has 5 mondays, then i want to return the 1st, 3rd and 5th monday date.
Someone ( @Anonymous ) on this forum gave me a great solution which i've pasted below and it did the trick, however, now i need return the pay period date for all contiguous dates in the dates column that falls within a range.
[Pay Period Number] = -- calculated column var __currentMonth = Dates[MonthNumber] -- month number 1,2,...,12 var __currentYear = Dates[Year] -- obvious var __currentDate = Dates[Date] var __numOfMondaysInMonth = COUNTROWS( filter( Dates, Dates[MonthNumber] = __currentMonth && Dates[Year] = __currentYear && Dates[DayName] = "Monday" -- you should have this field in your Dates ) ) var __isMonday = ( Dates[DayName] = "Monday" ) var __whichMonday = COUNTROWS( filter( Dates, Dates[MonthNumber] = __currentMonth && Dates[Year] = __currentYear && Dates[DayName] = "Monday" && Dates[Date] <= __currentDate ) ) var __result = if ( __isMonday, SWITCH( TRUE(), __numOfMondaysInMonth = 4, SWITCH( __whichMonday, 2, "2nd", 4, "4th" ) & " Pay Period", __numOfMondaysInMonth = 5, SWITCH( __whichMonday, 1, "1st", 2, "2nd", 5, "5th" ) & " Pay Period", "Check out the calendar for errors." ) ) return __result
What the solution above does is it returns the date of the relevant monday only.
What i want however is the following:
Say a month (i.e. July 2019) has 5 Mondays. This means, i want to return dates 01/07/2019 (1st monday of July), 15/07/2019 (3rd monday of July) & 29/07/2019 (5th monday of July). This is what the above solution does.
Now what i want to do is, for example, for dates between 18th June 2019 to 1st July 2019 (a 2 week period), I want to assign "01/07/19" against it all those dates.
For dates between 2nd July 2019 to 15th July 2019, I want to assign "15/07/2019" against all those dates, and same logic for the 16th July 2019 to 29th July 2019.
I want to apply this same logic for all dates in my dates table so that my custom pay period column has no blank values and all dates have an assigned pay period date assigned to it.
Thanks everyone and sorry for the long post.
Solved! Go to Solution.
Here's a file with how to do this in PQ:
https://1drv.ms/u/s!ApyQEauTSLtO6nGYmYQ4K0OFuPrz?e=t0U3yk
Bear in mind, though, that you've not given any rule as to what should happen when the calendar ends...
Best
Darek
Here's a file with how to do this in PQ:
https://1drv.ms/u/s!ApyQEauTSLtO6nGYmYQ4K0OFuPrz?e=t0U3yk
Bear in mind, though, that you've not given any rule as to what should happen when the calendar ends...
Best
Darek
Hi @Anonymous,
Sorry i'm still learning Power BI and this is really complex for me to grasp. Could I ask, how would i even do it in Power Query and why? Is it not possible to just manipulate the formula above? I thought this was a DAX issue as i have the base measure already that's returning a custom column?
I was using datesbetween function to return 14 days from whichever monday the date falls on but i keep getting an error. Is that the correct function to be using?
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |