Forum Discussion

kendrickp87's avatar
kendrickp87
Frequent Visitor
6 years ago
Solved

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 R...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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])

     

     

    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.

     

  • kendrickp87's avatar
    kendrickp87
    6 years ago

    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)
)

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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])

 

 

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.

 

kendrickp87's avatar
kendrickp87
Frequent Visitor
6 years ago

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)
)