Forum Discussion

yaman123's avatar
yaman123
Post Partisan
5 years ago
Solved

Employee Promotions

Hi,    I need help with a query to show employee promotions within a selected month.    I have a date table which is used as a slice to show data for that month. MAX(date) is used.    I have a ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi yaman123 ,

     

    So you can do some steps as follows.

    1. create a "Month" column in table "Date".

    month = MONTH('Date'[Date])

    2. create a measure and drag it into the filter of the table visual. (is 1)

    Measure = 

    var x1=SELECTEDVALUE('Date'[month])

    var x2=MAXX('Table 3',MONTH('Table 3'[FROM DATE]))

    return

    IF(x2>=x1-6&&x2<=x1,1,0)
    Result:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi yaman123 ,

 

Based on your description, you can do some steps as follows.

  1. Create a calculated table.

 

Table 3 =

SUMMARIZE (

    'Table 2',

    'Table 2'[EMP NO ],

    'Table 2'[NAME],

    "OLD ROLE", MAXX ( FILTER ( 'Table 2', YEAR ( 'Table 2'[VALID TO] ) < 3000 ), [JOB TITLE] ),

    "NEW ROLE", MAXX ( FILTER ( 'Table 2', YEAR ( 'Table 2'[VALID TO] ) > 3000 ), [JOB TITLE] ),

    "FROM DATE", MAX ( 'Table 2'[VALID FROM] )

)

 

2. Create a measure.

 

Measure =

var x1=SELECTEDVALUE('Date'[Date].[Month])

return

IF(MAXX('Table 3',[FROM DATE].[Month])=x1,1,0)

3. Create a table visual and drag the measure to “filters on this visual”.

 

Result:

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • yaman123's avatar
    yaman123
    Post Partisan
    5 years ago

    Hi Anonymous 

     

    Thanks for this

     

    But my measure is returning all zeros for all employees. I have a seperate date table not linked to any of the tables im using and i'm using the MonthYear column to select a date period. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi yaman123 ,

       

      Did you get the following table?

      Or can you share some screenshots around the issue please? This is not helping much.

      Add error screenshot as well which you are facing.

       

      Best Regards,

      Yuna

      • yaman123's avatar
        yaman123
        Post Partisan
        5 years ago

        Hi,

         

         

        I have created the table with this query

         

        Table 3 =
        SUMMARIZE(
        'Promotions and Transfers',
        'Promotions and Transfers'[EMP_NO],
        'Promotions and Transfers'[INTERNAL_DISPLAY_NAME],
        "OLD ROLE", MAXX(FILTER('Promotions and Transfers',YEAR('Promotions and Transfers'[VALID_TO]) < 3000),'Promotions and Transfers'[JOB_TITLE]),
        "NEW ROLE", MAXX(FILTER('Promotions and Transfers',YEAR('Promotions and Transfers'[VALID_TO]) > 3000), 'Promotions and Transfers'[JOB_TITLE]),
        "FROM DATE", MAX('Promotions and Transfers'[VALID_FROM])
        )
         
        I have created a measure in the created table:
         
        Measure =
        VAR x1 = SELECTEDVALUE('Date'[Date].[Month])
        return
        IF(MAXX('Table 3','Table 3'[FROM DATE].[Month]) = x1,1,0)
         
        I am getting the attached results when i select Aug 2020. My measure column is always displayed as '0' instead of showing '1' for selected month. All employees are showing instead of the promotions for that month
         
         
         
         
         
         
         
         
         
  • yaman123's avatar
    yaman123
    Post Partisan
    5 years ago

    Or if i can show the last 6 months of changes instead?