Forum Discussion
Pay Periods and Current Periods
- 7 years ago
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" ) ) ) - 7 years ago
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" ) ) ) */
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" )
)
)
*/
- Anonymous7 years agoNot applicable
Thank you so much. That was so helpful!