Re: MAX Value for Selected Slicer Values Measure

You won't be able to do any "time intelligence" without using a date datatype.

You can alter the Filter Context, but I'd advise to not put too much complex logic in your DAX.

You could use the subtract 2, but the problem you're running into is that 2-2 = 0. You want it to map to 52. You will somehow need to manipulate the values so that 02, 2019 = 54 (or some such logic). One possibility, without creating a separate table, is to create a Calculated Column year_period = period + (52 * (year - 2000)). Note: this won't work for years prior to 2000.

Re: MAX Value for Selected Slicer Values Measure

@natelpeterson You are brilliant sir! The data will not go below the year 200 so this this formula seems to be doing the trick where "Year_Period" is a calculated column of: Year_Period = Period + (52 * (Year - 2000):

Be careful when you reference this measure - the variable assumes that all of the selected pay periods are in scope. If the filter context is a single period, it won't work properly, because the MAX will return the single period in context.

Re: MAX Value for Selected Slicer Values Measure

I'm running into another issue however which I did not previosuly consider which is when the data crosses year or multiple years are selected.

Here is the current command:

MAX Pay Period Value =

VAR MAXPERIOD = MAX('Table'[Pay Period])

RETURN

CALCULATE([OT PREMIUM Measure],'Table'[Pay Period] = MAXPERIOD)

I'm running into 2 issues:

1. When multiple years are selected, I would like current Year to take precedence first, then "Pay Period". It appears to currently combine the OT Premium for the MAX Pay Period selected across multiple years. For example, if Pay Period 10 is selected and I have 2018 and 2019 selected, it aggregates the values for both years and displays that as the value.

2. I created another variation of the measure which takes the measure minus -2, to essentially allow me to compare against the prior pay period. When the first week of the year is selected it pulls no data for this value. I'm assuming incorporating the year logic would also accomodate this measure.

Re: MAX Value for Selected Slicer Values Measure

You won't be able to do any "time intelligence" without using a date datatype.

You can alter the Filter Context, but I'd advise to not put too much complex logic in your DAX.

You could use the subtract 2, but the problem you're running into is that 2-2 = 0. You want it to map to 52. You will somehow need to manipulate the values so that 02, 2019 = 54 (or some such logic). One possibility, without creating a separate table, is to create a Calculated Column year_period = period + (52 * (year - 2000)). Note: this won't work for years prior to 2000.

Re: MAX Value for Selected Slicer Values Measure

@natelpeterson You are brilliant sir! The data will not go below the year 200 so this this formula seems to be doing the trick where "Year_Period" is a calculated column of: Year_Period = Period + (52 * (Year - 2000):