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 data on a daily granularity and i need to create a pay period column that groups these daily dates into ranges. For example, i need to return pay period ending date for each date.
1st pay period ends on 15th of month and second ends on the end of the month.
Once i have this, i need to be able to dynamically assign a tag saying whether the pay period ending contains current date which is TODAY() and if it does, the entire pay period is it's tagged with a "CURRENT PERIOD" tag (a custom column), if not, it's tagged with "PREVIOUS PERIOD" or "FUTURE PERIOD" depending on whether the other pay periods exist ahead of behind the current period.
How do i achieve this?
Thanks all.
Solved! Go to Solution.
this should do
PayPeriod =
-- variable declaration section VAR __CurrentDate = 'Calendar'[Date] -- returns the date for current row (because it's calculated column)
-- output section RETURN IF( DAY(__CurrentDate) <= 15, -- checks if the day of current row date is <= 15 __CurrentDate - DAY(__CurrentDate) + 15 , -- if it is, it takes the date subtracts the number of days of this date (which gives 0) and adds 15 EOMONTH(__CurrentDate, 0) -- if it's not it gives the last day of the month )
Period =
-- variable declaration section VAR __CurrentPayPeriod = 'Calendar'[PayPeriod] -- returns the pay period for a current row VAR __Today = TODAY () -- returns the today's date VAR __TodayPayPeriod = -- returns the pay period for today (using method from [PayPeriod] IF ( DAY ( __Today ) <= 15, TODAY () + 15 - DAY ( TODAY () ), EOMONTH ( TODAY (), 0 ) ) VAR __PreviousPayPeriod = -- calculates the maximum date in PayPeriod column that is strictly before today's pay period CALCULATE ( MAX ( 'Calendar'[PayPeriod] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] < __TodayPayPeriod ) ) VAR __FuturePayPeriod = -- calculates the minimum date in PayPeriod column that is strictly after today's pay period CALCULATE ( MIN ( 'Calendar'[PayPeriod] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] > __TodayPayPeriod ) )
-- output section RETURN IF ( __TodayPayPeriod = __CurrentPayPeriod, "Current Period", IF ( __CurrentPayPeriod = __PreviousPayPeriod, "Previous Period", IF ( __CurrentPayPeriod = __FuturePayPeriod, "Future Period", "Other" ) ) )
I've added the comments in the accepted solution, lat me know if something is not clear. I use variables, they are explained in more detail here
https://www.sqlbi.com/articles/variables-in-dax/
also you can see what is calculated in each step by changing, what's returned, e.g. code below will return the today pay period, you can only return one thing at a time, I commented the actual answer in the code below)
Period = -- variable declaration section VAR __CurrentPayPeriod = 'Calendar'[PayPeriod] -- returns the pay period for a current row VAR __Today = TODAY () -- returns the today's date VAR __TodayPayPeriod = -- returns the pay period for today (using method from [PayPeriod] IF ( DAY ( __Today ) <= 15, TODAY () + 15 - DAY ( TODAY () ), EOMONTH ( TODAY (), 0 ) ) VAR __PreviousPayPeriod = -- calculates the maximum date in PayPeriod column that is strictly before today's pay period CALCULATE ( MAX ( 'Calendar'[PayPeriod] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] < __TodayPayPeriod ) ) VAR __FuturePayPeriod = -- calculates the minimum date in PayPeriod column that is strictly after today's pay period CALCULATE ( MIN ( 'Calendar'[PayPeriod] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] > __TodayPayPeriod ) ) -- output section RETURN __TodayPayPeriod /* IF ( __TodayPayPeriod = __CurrentPayPeriod, "Current Period", IF ( __CurrentPayPeriod = __PreviousPayPeriod, "Previous Period", IF ( __CurrentPayPeriod = __FuturePayPeriod, "Future Period", "Other" ) ) ) */
you may need to change the 'Calendar'[Date] to whatever would be your date column
PayPeriod = VAR __CurrentDate = 'Calendar'[Date] RETURN IF( DAY(__CurrentDate) <= 15, __CurrentDate - DAY(__CurrentDate) + 15 , EOMONTH(__CurrentDate, 0) )
This column uses the previous one, so it should be added later
Period = VAR __CurrentPayPeriod = 'Calendar'[PayPeriod] VAR __Today = TODAY() VAR __TodayPayPeriod = IF(DAY(__Today) <= 15, TODAY() - DAY(TODAY()) + 15, EOMONTH(TODAY(), 0)) RETURN IF(__TodayPayPeriod=__CurrentPayPeriod, "Current Period", IF(__CurrentPayPeriod < __TodayPayPeriod, "Previous Period", "Future Period"))
Hi @Stachu ,
Thank you so much! This was exactly what i was after!
I did have one last question though. Basically, i want to have a slicer that shows "current period", "past period" and "future period". When i select current period, it shouldof course show me that current pay period sales and given that there can only be one 'current period' at a time in the date table, it will always show output for one pay period.
But for the "past period" and "future period" tags, i want it to always just show me 1 past period from the current period and 1 future period from the current period, not an aggregation of sales across all past period and future periods.
How do i make it so that based on whatever the current period is, it's only showing me one period prior to current period and one period future of curent period and doing it dynamically as current period will change dynamically.
Thanks so much!
this should do
PayPeriod =
-- variable declaration section VAR __CurrentDate = 'Calendar'[Date] -- returns the date for current row (because it's calculated column)
-- output section RETURN IF( DAY(__CurrentDate) <= 15, -- checks if the day of current row date is <= 15 __CurrentDate - DAY(__CurrentDate) + 15 , -- if it is, it takes the date subtracts the number of days of this date (which gives 0) and adds 15 EOMONTH(__CurrentDate, 0) -- if it's not it gives the last day of the month )
Period =
-- variable declaration section VAR __CurrentPayPeriod = 'Calendar'[PayPeriod] -- returns the pay period for a current row VAR __Today = TODAY () -- returns the today's date VAR __TodayPayPeriod = -- returns the pay period for today (using method from [PayPeriod] IF ( DAY ( __Today ) <= 15, TODAY () + 15 - DAY ( TODAY () ), EOMONTH ( TODAY (), 0 ) ) VAR __PreviousPayPeriod = -- calculates the maximum date in PayPeriod column that is strictly before today's pay period CALCULATE ( MAX ( 'Calendar'[PayPeriod] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] < __TodayPayPeriod ) ) VAR __FuturePayPeriod = -- calculates the minimum date in PayPeriod column that is strictly after today's pay period CALCULATE ( MIN ( 'Calendar'[PayPeriod] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] > __TodayPayPeriod ) )
-- output section RETURN IF ( __TodayPayPeriod = __CurrentPayPeriod, "Current Period", IF ( __CurrentPayPeriod = __PreviousPayPeriod, "Previous Period", IF ( __CurrentPayPeriod = __FuturePayPeriod, "Future Period", "Other" ) ) )
I've added the comments in the accepted solution, lat me know if something is not clear. I use variables, they are explained in more detail here
https://www.sqlbi.com/articles/variables-in-dax/
also you can see what is calculated in each step by changing, what's returned, e.g. code below will return the today pay period, you can only return one thing at a time, I commented the actual answer in the code below)
Period = -- variable declaration section VAR __CurrentPayPeriod = 'Calendar'[PayPeriod] -- returns the pay period for a current row VAR __Today = TODAY () -- returns the today's date VAR __TodayPayPeriod = -- returns the pay period for today (using method from [PayPeriod] IF ( DAY ( __Today ) <= 15, TODAY () + 15 - DAY ( TODAY () ), EOMONTH ( TODAY (), 0 ) ) VAR __PreviousPayPeriod = -- calculates the maximum date in PayPeriod column that is strictly before today's pay period CALCULATE ( MAX ( 'Calendar'[PayPeriod] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] < __TodayPayPeriod ) ) VAR __FuturePayPeriod = -- calculates the minimum date in PayPeriod column that is strictly after today's pay period CALCULATE ( MIN ( 'Calendar'[PayPeriod] ), FILTER ( ALL ( 'Calendar' ), 'Calendar'[PayPeriod] > __TodayPayPeriod ) ) -- output section RETURN __TodayPayPeriod /* IF ( __TodayPayPeriod = __CurrentPayPeriod, "Current Period", IF ( __CurrentPayPeriod = __PreviousPayPeriod, "Previous Period", IF ( __CurrentPayPeriod = __FuturePayPeriod, "Future Period", "Other" ) ) ) */
Thank you so much. That was so helpful!
Hi @Stachu ,
THIS WORKED PERFECTLY! Thank you!!
I'm still new to Power BI. Could you explain the general logic of what you did? Thanks so much!
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 |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |