cancel
Showing results for
Did you mean:
Frequent Visitor

## Employee Grade level YTD Comparison

Hi,
I am trying to highlight new employees and promotions in a matrix table. I am able to show Current Month vs. Prior but wanted to give my customers the opportunity to pivot between MTD (Current Roster - Prior Mo Grade) & YTD (Current Roster - Jan Grade) with a slicer. The YTD Section of my dynamic formula (show January grade levels) needs some help.

I would like it to show Jan Employee grade levels in the same line as the current month (the report has a month & year filter). Any help would greatly be appreciated!

``````Prev Grade Dynamic =
"MTD",
VAR _prevDate =
CALCULATE(
MAX(CORPX_ROSTER_V[MonthNo]),
FILTER(
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),
CORPX_ROSTER_V[Date] < MAX(CORPX_ROSTER_V[Date])
)
)
Return
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _prevDate),

"YTD",
VAR _JanDate =
CALCULATE(
MAX(CORPX_ROSTER_V[MonthNo]),
FILTER(
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),
CORPX_ROSTER_V[BOOKMO] = "201901")
)
Return
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _JanDate)
)``````

Image 1) MTD & works properly
Image 2) YTD & Does not work

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support

## Re: Employee Grade level YTD Comparison

MTD (Current Roster - Prior Mo Grade) & YTD (Current Roster - Jan Grade)

Create measures

``````mtd =
IF (
DATEDIFF ( MAX ( Sheet2[date] ), TODAY (), MONTH ) <= 1,
CALCULATE (
SUM ( Sheet2[value] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[pin] ),
DATEDIFF ( Sheet2[date], TODAY (), MONTH ) <= 1
)
)
)

ytd = TOTALYTD(SUM(Sheet2[value]),'date'[Date],FILTER(ALLEXCEPT(Sheet2,Sheet2[pin]),Sheet2[date]<=TODAY()),"12/31")

Measure = SWITCH(MAX(slicer[selection]),"MTD",[mtd],"YTD",[ytd])
``````

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

## Re: Employee Grade level YTD Comparison

Thank you Maggie, that worked for me!

I was also able to solve my own question using the MIN BookMonth #, to get January's roster/ employee grade on the same row as current month's values.

``````Prev Grade Dynamic =
"MTD",
VAR _prevDate =
CALCULATE(
MAX(CORPX_ROSTER_V[MonthNo]),
FILTER(
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),
CORPX_ROSTER_V[Date] < MAX(CORPX_ROSTER_V[Date])
)
)
Return
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _prevDate),

"YTD",
VAR _JanDate =
CALCULATE(
MIN(CORPX_ROSTER_V[MonthNo]),
FILTER(
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),
CORPX_ROSTER_V[Date] < MAX(CORPX_ROSTER_V[Date])
)
)
Return
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _JanDate)
)``````

4 REPLIES 4
Frequent Visitor

## DAX Employee Grade level YTD Comparison

Hi,
I am trying to highlight new employees and promotions in a matrix table. I am able to show Current Month vs. Prior but wanted to give my customers the opportunity to pivot between MTD (Current Roster - Prior Mo Grade) & YTD (Current Roster - Jan Grade) with a slicer. The YTD Section of my dynamic formula (show January grade levels) needs some help.

I would like it to show Jan Employee grade levels in the same line as the current month (the report has a month & year filter). Any help would greatly be appreciated!

Thank you,

Kendrick

``````Prev Grade Dynamic =
"MTD",
VAR _prevDate =
CALCULATE(
MAX(CORPX_ROSTER_V[MonthNo]),
FILTER(
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),
CORPX_ROSTER_V[Date] < MAX(CORPX_ROSTER_V[Date])
)
)
Return
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _prevDate),

"YTD",
VAR _JanDate =
CALCULATE(
MAX(CORPX_ROSTER_V[MonthNo]),
FILTER(
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),
CORPX_ROSTER_V[BOOKMO] = "201901")
)
Return
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _JanDate)
)``````

``````Grade = TOTALMTD(
'Calendar'[Date])``````
``Grade Var = [Grade] - [Prev Grade Dynamic]``
``````Grade Change Description = IF([Grade Var] > 0 && [Grade Var] < [Grade], "Promotion",
IF([Grade Var] < 0, "Recalibration", "")))``````

Image 1) MTD & works properly
Image 2) YTD & Does not work

Community Support

## Re: Employee Grade level YTD Comparison

MTD (Current Roster - Prior Mo Grade) & YTD (Current Roster - Jan Grade)

Create measures

``````mtd =
IF (
DATEDIFF ( MAX ( Sheet2[date] ), TODAY (), MONTH ) <= 1,
CALCULATE (
SUM ( Sheet2[value] ),
FILTER (
ALLEXCEPT ( Sheet2, Sheet2[pin] ),
DATEDIFF ( Sheet2[date], TODAY (), MONTH ) <= 1
)
)
)

ytd = TOTALYTD(SUM(Sheet2[value]),'date'[Date],FILTER(ALLEXCEPT(Sheet2,Sheet2[pin]),Sheet2[date]<=TODAY()),"12/31")

Measure = SWITCH(MAX(slicer[selection]),"MTD",[mtd],"YTD",[ytd])
``````

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

## Re: Employee Grade level YTD Comparison

Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.

Best Regards
Maggie
Frequent Visitor

## Re: Employee Grade level YTD Comparison

Thank you Maggie, that worked for me!

I was also able to solve my own question using the MIN BookMonth #, to get January's roster/ employee grade on the same row as current month's values.

``````Prev Grade Dynamic =
"MTD",
VAR _prevDate =
CALCULATE(
MAX(CORPX_ROSTER_V[MonthNo]),
FILTER(
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),
CORPX_ROSTER_V[Date] < MAX(CORPX_ROSTER_V[Date])
)
)
Return
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _prevDate),

"YTD",
VAR _JanDate =
CALCULATE(
MIN(CORPX_ROSTER_V[MonthNo]),
FILTER(
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),
CORPX_ROSTER_V[Date] < MAX(CORPX_ROSTER_V[Date])
)
)
Return
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _JanDate)
)``````

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors