return value (text format) for previous qtr

[Marshmallow]

Hi, I need help with dax formula to return previous period with this messy data that I have. Can someone please help?

Below is my data (sample)

Region	Company	Type	Period	Rating
Sydney East Metro	APP	G	Jun-24	HIGH
Sydney East Metro	APP	G	Mar-24	MOD
Sydney East Metro	APP	G	Dec-23	HIGH
Sydney East Metro	APP	G	Sep-23	HIGH
Sydney East Metro	APP	G	Jun-23	MOD
Sydney East Metro	Asur	G	Jun-24	LOW
Sydney East Metro	Asur	G	Mar-24	MOD
Sydney East Metro	Asur	G	Dec-23	MOD
Sydney East Metro	Asur	G	Sep-23	MOD
Sydney East Metro	Asur	G	Jun-23	MOD
Sydney East Metro	AW	G	Jun-24	MOD
Sydney East Metro	AW	G	Mar-24	MOD
Sydney East Metro	AW	G	Dec-23	MOD
Sydney East Metro	AW	G	Sep-23	MOD
Sydney East Metro	AW	G	Jun-23	MOD
Sydney East Metro	MAX	EO	Jun-24	LOW
Sydney East Metro	MAX	EO	Mar-24	LOW
Sydney East Metro	MAX	EO	Dec-23	LOW
Sydney East Metro	MAX	EO	Sep-23	MOD
Sydney East Metro	MAX	EO	Jun-23	MOD
Sydney East Metro	MCD	G	Jun-24	MOD
Sydney East Metro	MCD	G	Mar-24	MOD
Sydney East Metro	MCD	G	Dec-23	MOD
Sydney East Metro	MCD	G	Sep-23	MOD
Sydney East Metro	MCD	G	Jun-23	MOD
Sydney South West	Global	G	Jun-24	LOW
Sydney South West	Global	G	Mar-24	LOW
Sydney South West	Global	G	Dec-23	LOW
Sydney South West	Global	G	Sep-23	LOW
Sydney South West	Global	G	Jun-23	LOW
Sydney South West	Global	EO	Jun-24	LOW
Sydney South West	Global	EO	Mar-24	LOW
Sydney South West	Global	EO	Dec-23	MOD
Sydney South West	Global	EO	Sep-23	LOW
Sydney South West	Global	EO	Jun-23	LOW
Sydney South West	Jobs	G	Jun-24	HIGH
Sydney South West	Jobs	G	Mar-24	LOW
Sydney South West	Jobs	G	Dec-23	MOD
Sydney South West	Jobs	G	Sep-23	MOD
Sydney South West	Jobs	G	Jun-23	LOW
Sydney South West	Matching	G	Jun-24	MOD
Sydney South West	Matching	G	Mar-24	MOD
Sydney South West	Matching	G	Dec-23	MOD
Sydney South West	Matching	G	Sep-23	MOD
Sydney South West	Matching	G	Jun-23	MOD
Sydney South West	SR	G	Jun-24	MOD
Sydney South West	SR	G	Mar-24	MOD
Sydney South West	SR	G	Dec-23	MOD
Sydney South West	SR	G	Sep-23	MOD
Sydney South West	SR	G	Jun-23	MOD

I need to have the return outcome as below (last column). Can someone please help :). Tia.

Region	Company	Type	Period	Rating	Previous Qtr Performance
Sydney East Metro	APP	G	Jun-24	HIGH	MOD
Sydney East Metro	APP	G	Mar-24	MOD	HIGH
Sydney East Metro	APP	G	Dec-23	HIGH	HIGH
Sydney East Metro	APP	G	Sep-23	HIGH	MOD
Sydney East Metro	APP	G	Jun-23	MOD	N/A
Sydney East Metro	Asur	G	Jun-24	LOW	MOD
Sydney East Metro	Asur	G	Mar-24	MOD	MOD
Sydney East Metro	Asur	G	Dec-23	MOD	MOD
Sydney East Metro	Asur	G	Sep-23	MOD	MOD
Sydney East Metro	Asur	G	Jun-23	MOD	N/A
Sydney East Metro	AW	G	Jun-24	MOD	MOD
Sydney East Metro	AW	G	Mar-24	MOD	MOD
Sydney East Metro	AW	G	Dec-23	MOD	MOD
Sydney East Metro	AW	G	Sep-23	MOD	MOD
Sydney East Metro	AW	G	Jun-23	MOD	N/A

[v-junyant-msft · Accepted solution]

Hi @Marshmallow ,

Please try this way:
First use this DAX to create a calculated column:

RANK = 
VAR _Region = [Region]
VAR _Company = [Company]
VAR _Type = [Type]
RETURN
RANKX(
    FILTER(
        ALL('Table'),
        'Table'[Region] = _Region && 'Table'[Company] = _Company && 'Table'[Type] = _Type
    ),
    'Table'[Period],
    ,
    DESC,
    Dense
)

Then use this DAX to create another calculated column:

Previous Qtr = 
VAR _Region = [Region]
VAR _Company = [Company]
VAR _Type = [Type]
VAR _Rank = [RANK]
RETURN
CALCULATE(
    MAX('Table'[Rating]),
    ALL('Table'),
    'Table'[Region] = _Region && 'Table'[Company] = _Company && 'Table'[Type] = _Type && 'Table'[RANK] = _Rank + 1
)

And the final output is as below:

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

[Marshmallow]

Thank you, it works 🙂

[Kedar_Pande]

@MarshmallowCould you share the file with me? I'll design it and send it back to you.

[Marshmallow]

I have never shared a file here 🤔. How do I go about it?

[Kedar_Pande]

upload the file to google drive and share the link here

[Marshmallow]

try this - hope it works 

https://drive.google.com/file/d/1Yn5sVsys9KhycfUSqYVLFbOv3yzmFZc2/view?usp=drive_link

[Kedar_Pande]

Approve the access request

[amitchandak]

@Marshmallow , I would prefer to create date using period 

Date = datevalue("01-" &[Period])

I can join this date with date of date table and use time intelligence

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0

[Marshmallow]

can you please create it in the power bi and send the file so i can follow it through? i have issue creating the date too