DAX to return Status (string column) of previous month

[ptewary]

Hi!

I have the following table:

MONTH	STATUS
18-Jan	Open
18-Feb	WIP
18-Mar	Closed
18-Apr	Open
18-May	WIP
18-Jun	Closed
18-Jul	Open
18-Aug	WIP
18-Sep	Closed
18-Oct	Open
18-Nov	WIP
18-Dec	Closed

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

MONTH	STATUS	STATUS (PREVIOUS MONTH)
18-Jan	Open
18-Feb	WIP	Open
18-Mar	Closed	WIP
18-Apr	Open	Closed
18-May	WIP	Open
18-Jun	Closed	WIP
18-Jul	Open	Closed
18-Aug	WIP	Open
18-Sep	Closed	WIP
18-Oct	Open	Closed
18-Nov	WIP	Open
18-Dec	Closed	WIP

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.

[mohittimpus · Accepted solution]

Hii @ptewary, 

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

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

[mohittimpus · Accepted solution]

Hii @ptewary, 

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

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

[ptewary · Accepted solution]

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!