Need help with DAX to calculate last year Rating of current year active employees

[RolinMartis]

HI Team,

I have a employee level Rating data for each quarter.

I need to build a table/Matrix in which i have to show all the employees in latest quarter and rating in current quarter and last year any particular quarter (Q1) 

If any newly hired person whos data is not there in Q1 then Rating should be blank.

My sample data looks like below

Worker	Quarter Name	IPM	latest Rating
a	FY2021Q4	1.01	Strong
a	FY2021Q3	1.02	Ourstanding
a	FY2021Q2	1.03	Stong
a	FY2021Q1	1	Outstanding
b	FY2021Q4	1.22	Strong
b	FY2021Q3	1.32	Strong
b	FY2021Q2	1.21	Strong
b	FY2021Q1	1	Strong
c	FY2021Q4	1.04	Strong
c	FY2021Q3	1.01	Strong
c	FY2021Q2	1.32	Needs Improvement
c	FY2021Q1	1.22	Needs Improvement
d	FY2021Q4	1.32	Outstanding
d	FY2021Q3	1.21	Strong
d	FY2021Q2	1	Needs Improvement
e	FY2021 Q2	1.2	Needs Improvement

My matrix/table in powerBI report should be like below

Here i have list of all employees in FY2021Q4. worker "e" is not included because he is not active in FY2021Q4

worker "d" has only the IPM data for Q4 and IPM FY2021Q1 blank because he does not have data for that quarter 

Can anyone help me how to implement such logic.

Regards,

Rolin

[v-yiruan-msft · Accepted solution]

Hi @RolinMartis ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a two measures as below:

IPM FY2021Q4 = CALCULATE(SUM('Table'[IPM]),'Table'[Quarter Name]="FY2021Q4")

IPM FY2021Q1 = CALCULATE(SUM('Table'[IPM]),'Table'[Quarter Name]="FY2021Q1")

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards