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,
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 =
SWITCH(MAX('Fiscal Period'[Grade Scenario]),
"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
CALCULATE(MAX(CORPX_ROSTER_V[Job Code to Grade.Grade Equivalent]),
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
CALCULATE(MAX(CORPX_ROSTER_V[Job Code to Grade.Grade Equivalent]),
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _JanDate)
)
Image 1) MTD & works properly
Image 2) YTD & Does not work
Solved! Go to Solution.
Hi @kendrickp87
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])
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 =
SWITCH(MAX('Fiscal Period'[Grade Scenario]),
"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
CALCULATE(MAX(CORPX_ROSTER_V[Grade Equivalent]),
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
CALCULATE(MAX(CORPX_ROSTER_V[Grade Equivalent]),
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _JanDate)
)
Hi @kendrickp87
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 =
SWITCH(MAX('Fiscal Period'[Grade Scenario]),
"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
CALCULATE(MAX(CORPX_ROSTER_V[Job Code to Grade.Grade Equivalent]),
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
CALCULATE(MAX(CORPX_ROSTER_V[Job Code to Grade.Grade Equivalent]),
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _JanDate)
)
For added Context:
Grade = TOTALMTD(
SUM(CORPX_ROSTER_V[Job Code to Grade.Grade Equivalent]),
'Calendar'[Date])
Grade Var = [Grade] - [Prev Grade Dynamic]
Grade Change Description = IF([Grade Var] > 0 && [Grade Var] < [Grade], "Promotion",
IF([Grade Var] > 0 && [Grade Var] = [Grade], "New Hire/ Transfer",
IF([Grade Var] < 0, "Recalibration", "")))
Image 1) MTD & works properly
Image 2) YTD & Does not work
Hi @kendrickp87
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])
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 =
SWITCH(MAX('Fiscal Period'[Grade Scenario]),
"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
CALCULATE(MAX(CORPX_ROSTER_V[Grade Equivalent]),
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
CALCULATE(MAX(CORPX_ROSTER_V[Grade Equivalent]),
ALLEXCEPT(CORPX_ROSTER_V, CORPX_ROSTER_V[PIN]),CORPX_ROSTER_V[MonthNo] = _JanDate)
)
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |