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.

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
     
     
     
     
     
     
     
     
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi yaman123 ,

       

      You said that"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. " The field I used in the slicer is the "Date" column. 

       

      the attachment file

       

      Best Regards,

      Yuna

      • yaman123's avatar
        yaman123
        Post Partisan
        5 years ago

        So would i need to use the date column instead of the month year column from the date table?