Previous Month Category

[BernardWilliam]

Hi,

I have a dataset of about 2 million rows and 20 colunms split across 12 months. Each item appears only once per month and can have the status new, open or closed. I want to keep track of the previous month status of each item. Currently I use measure  and a date table

I am not 100% sure if I should have an ALLEXCEPT clause here or not, but it appears to be working fine. There is an issue that occurs when I filter by Status while displaying the previous and current status side by side

I would like the blank rows for current status not to appear; I think it may be to so with the evaluation context of the measure that they are there and I know that these blank rows have the current status of closed for July.

To attempt to workaround this I tried to use the same code as the measure above to create a calculated column, but I got a error about not enough memory.

I have also tried the below, but it does not give me the correct results

I would be grateful if someone could give me a solution for creating a calculated column or way of using a measure that can be filtered by status.

Thanks,

Bernard

[Greg_Deckler]

@BernardWilliam - Surprised that you got an out of memory error on just 2 million lines of code. Perhaps

Column = 
  VAR __PreviousMonth = EOMONTH([ReportingMonth],-1)
  VAR __Status = CALCULATE(MAX([Status]),[ReportingMonth]=__PreviousMonth)
RETURN
  __Status

Hard to be sure because not 100% on your data. Can you post sample of the critical fields, like ReportingMonth? Do you have a status per day or ? Do you care about having multiple years? 

Might be:

Column =
  VAR __PreviousMonth = MONTH(EOMONTH([ReportingDate],-1)
  VAR __Status = CALCULATE(MAX([Status]),MONTH([ReportingDate])=__PreviousMonth)

[BernardWilliam]

Thank you for your reply @Greg_Deckler . The solutions have not worked as yet. I think it may down to the data type of the report month.

EOMONTH([ReportMonth],-1) looks to give the right value but then but then the calculate statement returns empty. If I make a column of EOMONTH([ReportMonth],-1) and drag it to a  table with Report Month they appear differently formatted.

ReportMonth just contains the date of the last day of the month over time. There can be the same month over different years (july 2019 and July 2020 for example) so year does matter.  I have gave a small sample of the key fields below hopefully this will be sufficient?

IDStatusReport Month

95294	Closed	29/02/2020
95294	Closed	31/01/2020
95294	Closed	31/12/2019
95294	Open	30/11/2019
95294	Closed	31/10/2019
95295	Closed	29/02/2020
95295	New	31/01/2020
95295	Open	31/12/2019
95377	Closed	29/02/2020
95377	New	31/01/2020
95377	Open	31/12/2019
95377	New	30/11/2019

Thanks,

Bernard

[BernardWilliam]

Thanks for your help @Greg_Deckler . I just need to add ALLEXCEPT(Table1,Table1[ID]) and your solution worked.

[BernardWilliam]

@AnonymousThank you for the solution and advice.

Bernard

[Anonymous · Accepted solution]

[Status PM] = // calc column
var __id = T[ID]
var __month = T[Month] // must be datetime
RETURN
// This will return the status
// on the date preceding the
// current date. If there are no
// temporal gaps in your data,
// you'll get what you need.
MAXX(
	TOPN(1,
		filter(
			T,
			T[ID] = __id
			&&
			T[Month] < __month
		),
		T[Month],
		DESC
	),
	T[Status]
)

One piece of good advice: NEVER use CALCULATE in calculated columns in a fact table. If the fact table is even moderately big, you'll very likely get an out-of-memory error. And also, please note that such calculations should be performed in Power Query (where they'll be fast and efficient), not in DAX. Calculated columns should be calculated in DAX only when there's absolutely no other way. One reason for this is that such columns don't get compressed optimally if calculated in DAX, hence reducing the speed of calculations.

[amitchandak]

@BernardWilliam , If I got you correctly

Final previous Status

If (isblank([Current status]) , blank(), [previous Status])

I Hope , [previous Status] is correct.

Remember display is a superset of measure. Any other measure can force the display of blank.