Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ptewary
Frequent Visitor

DAX to return Status (string column) of previous month

Hi!

 

I have the following table:

 

MONTHSTATUS
18-JanOpen
18-FebWIP
18-MarClosed
18-AprOpen
18-MayWIP
18-JunClosed
18-JulOpen
18-AugWIP
18-SepClosed
18-OctOpen
18-NovWIP
18-DecClosed

 

I want to have a column that returns the status of the previous month for each row like below:

 

MONTHSTATUSSTATUS (PREVIOUS MONTH)
18-JanOpen 
18-FebWIPOpen
18-MarClosedWIP
18-AprOpenClosed
18-MayWIPOpen
18-JunClosedWIP
18-JulOpenClosed
18-AugWIPOpen
18-SepClosedWIP
18-OctOpenClosed
18-NovWIPOpen
18-DecClosedWIP

 

I tried to create a Calculated column using the following DAX:

 

PreviousMonthStatus = VAR __prevDate = PREVIOUSMONTH(Sheet1[MONTH].[Date])
RETURN
CALCULATE( VALUES( 'Sheet1'[STATUS] ), FILTER(Sheet1, 'Sheet1'[MONTH].[Date] = __prevDate ))
 
But it gives me a "Circular Dependency" error. Where am I going wrong?
 
Thanks in advance.
2 ACCEPTED SOLUTIONS
mohittimpus
Helper V
Helper V

Hii @ptewary

Create an index column for "Status" in edit query as shown in image:

 

image.png

 

Then create a calculated column in your table:

PreviousText... = CALCULATE(MAX(Table[STATUS]),FILTER(Table,Table[Index]=EARLIER(Table[Index])-1))
 
If this helps, please mark it as Accept as Solution!
Thanks,
mohittimpus

View solution in original post

Thanks @mohittimpus 

 

This is basically the solution but the only issue is that what if the dates are not in order, then using the Index column would not be helpful. But nonetheless, using the EARLIER function helped to tackle the Circular Dependency issue. So, I tweaked my DAX formula to make it:

 

PreviousMonthStatus = CALCULATE(MAX(Sheet1[STATUS]),FILTER(Sheet1,Sheet1[MONTH].[MonthNo]=EARLIER(Sheet1[MONTH].[MonthNo])-1))
 
Worked like a charm.
 
Thanks a lot!

View solution in original post

2 REPLIES 2
mohittimpus
Helper V
Helper V

Hii @ptewary

Create an index column for "Status" in edit query as shown in image:

 

image.png

 

Then create a calculated column in your table:

PreviousText... = CALCULATE(MAX(Table[STATUS]),FILTER(Table,Table[Index]=EARLIER(Table[Index])-1))
 
If this helps, please mark it as Accept as Solution!
Thanks,
mohittimpus

Thanks @mohittimpus 

 

This is basically the solution but the only issue is that what if the dates are not in order, then using the Index column would not be helpful. But nonetheless, using the EARLIER function helped to tackle the Circular Dependency issue. So, I tweaked my DAX formula to make it:

 

PreviousMonthStatus = CALCULATE(MAX(Sheet1[STATUS]),FILTER(Sheet1,Sheet1[MONTH].[MonthNo]=EARLIER(Sheet1[MONTH].[MonthNo])-1))
 
Worked like a charm.
 
Thanks a lot!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.