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

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

     

4 Replies

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

       

  • kendrickp87's avatar
    kendrickp87
    Frequent Visitor
    6 years ago

    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

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

    Hi kendrickp87 

    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