Dynamic measure working days for specific periods of time without date field

[MBisceglia]

Good morning everyone,  

I'm trying to create dynamic measures that will help me determine if an employee worked 180 days or more in each of the preceding 12-month periods. The idea here is to check this for the last 3 years and I would like to be able to run this analysis every quarter. Right now my analysis' timeframe starts last quarter (Q1 2022) and ends in Q2 2019. Therefore, I need to know if in the last 3 years each employee has worked 180 days in each of the following three periods:

1. Q2, Q3, Q4 2021 and Q1 2022 -->Year 1
2. Q2, Q3, Q4 2020 and Q1 2021 --> Year 2
3. Q2, Q3, Q4 2019 and Q1 2020 --> Year 3

So, it is basically a rolling 12-month calendar, since next quarter the timeframe analyzed will start in Q2 2022 and end in Q3 2019.

My problem is that I'm working with a dataset that is summarized by month, so I don't have a date field (MM-DD-YYYY). Here's what the data looks like:

the workaround I found was to create a metric using the YearQuarter# numeric value to establish the different periods but this is not dynamic, meaning that if I want to run this again next quarter I will have to manually modify the measure's substracting numbers:

Year 1 =

VAR maxquarter= max(WorkDays[YearQuarter#])

return

CALCULATE(SUM(WorkDays[Total Operating Days]),OR(WorkDays[YearQuarter#] =maxquarter,OR(WorkDays[YearQuarter#]=maxquarter-7, OR(WorkDays[YearQuarter#]=maxquarter-8,WorkDays[YearQuarter#]=maxquarter-9))))

I thought about creating nested IF() functions based on the last digit of the "maxquarter" variable I created but didn't work I tried to do the following :

VAR maxquarter= max(WorkDays[YearQuarter#])

VAR quarter1= Right(max(WorkDays[YearQuarter#]),1)

return

if([maxqurter test]=1,
CALCULATE(SUM(WorkDays[Total Operating Days]),OR(WorkDays[YearQuarter#] =maxquarter,OR(WorkDays[YearQuarter#]=maxquarter-7, OR(WorkDays[YearQuarter#]=maxquarter-8,WorkDays[YearQuarter#]=maxquarter-9)))),

if([maxqurter test]=2, CALCULATE(SUM(WorkDays[Total Operating Days]),OR(WorkDays[YearQuarter#] =maxquarter,OR(WorkDays[YearQuarter#]=maxquarter-1, OR(WorkDays[YearQuarter#]=maxquarter-8,WorkDays[YearQuarter#]=maxquarter-9)))),

IF([maxqurter test]=3,CALCULATE(SUM(WorkDays[Total Operating Days]),OR(WorkDays[YearQuarter#] =maxquarter,OR(WorkDays[YearQuarter#]=maxquarter-1, OR(WorkDays[YearQuarter#]=maxquarter-2,WorkDays[YearQuarter#]=maxquarter-9)))),

if([maxqurter test] =4, CALCULATE(SUM(WorkDays[Total Operating Days]),OR(WorkDays[YearQuarter#] =maxquarter,OR(WorkDays[YearQuarter#]=maxquarter-1, OR(WorkDays[YearQuarter#]=maxquarter-2,WorkDays[YearQuarter#]=maxquarter-3))))))))

Probably too long for a DAX code, does anyone have any suggestions on how to make this measure dynamic without having to manually change the CALCULATE function's subtracting numbers every quarter?

[johnt75 · Accepted solution]

Set up a date table and include a column in the format of either of the year / quarter columns on your existing table. Create a one-to-many relationship from Date to the current table and use the columns from the Date table in filters, slicers and calculations.

[MBisceglia · Accepted solution]

Hi @johnt75 !

Thanks! your reply inspired me. I eneded up seting up a date table and then using the following for Year 1 and the rest of the years: 

Year 1 =

VAR lastestdate= MAX('Date'[Date])

VAR startdate = DATE(YEAR(lastestdate), MONTH(lastestdate),DAY(lastestdate))

return

CALCULATE(SUM(WorkDays[Total Operating Days]), DATESINPERIOD('Date'[Date],startdate,-1,YEAR))

Year 2 to 10 =

VAR lastestdate= MAX('Date'[Date])

VAR startdate = EDATE(lastestdate,-12)

return

CALCULATE(SUM(WorkDays[Total Operating Days]), DATESINPERIOD('Date'[Date],startdate,-1,YEAR))

[johnt75 · Accepted solution]

Set up a date table and include a column in the format of either of the year / quarter columns on your existing table. Create a one-to-many relationship from Date to the current table and use the columns from the Date table in filters, slicers and calculations.