Forum Discussion

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago

Promotions and Transfers

Hi,   I would like to know how I would be able to create a report based on employee promotions and transfers.  I have the below dataset and would like to show the employees who have had a transfer ...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi yaman123 ,

 

First create 2 columns as below:

Column_valid from = FORMAT('Table'[VALID_FROM],"MMM")&" "&YEAR('Table'[VALID_FROM])
Column_valid to = FORMAT('Table'[VALID_TO],"MMM")&" "&YEAR('Table'[VALID_TO])

Then create a new table as below:

Table 2 = UNION(VALUES('Table'[Column_valid from]),VALUES('Table'[Column_valid to]))

And a measure as below:

_name = CALCULATE(MAX('Table'[NAME]),FILTER('Table',('Table'[Column_valid from]=SELECTEDVALUE('Table 2'[Column_valid from])||'Table'[Column_valid to]=SELECTEDVALUE('Table 2'[Column_valid from]))&&'Table'[DATE_OF_LEAVING]=DATE("9999","12","31")))

And you will see:

For the related .pbix file,pls see here.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

yaman123's avatar
yaman123
Icon for Post Partisan rankPost Partisan
5 years ago

Hi, 

 

Thanks for that. I have a date table which is used to select a month year to show data for. I would like the transfers table to show old job and new job columns if possible so its only one line per employee for the chosen month year? How would i be able to achieve that? 

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi yaman123 ,

     

    Not quite clear,could you pls provide an expected output?

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

    • yaman123's avatar
      yaman123
      Icon for Post Partisan rankPost Partisan
      5 years ago

      Hi, I would like to show the below when April 2020 is selected from the date table 

       

      Name             Old Position                                                          New Position                   Valid From

      Employee D   Business Improvement and Planning Manager     Head of Supply Chain     28/04/2020