topic Re: Create a Column using DAX that flags if a row existed in the previous month based on a ID Field in DAX Commands and Tips

Create a Column using DAX that flags if a row existed in the previous month based on a ID Field

Anonymous — Mon, 15 Jul 2019 11:57:36 GMT

Hi All

I want to create a Column that flags if a record existed in the previous month, so that if it didn't I can exclude it from the current months calculations.

ID     Month

1       Jun-19

2       Jun-19

3       Jun-19

4       Jun-19

1       May-19

2       May-19

3      May-19

So in the example above, I want 3rd Column that tells me for example that ID 4 did not exist in May

I want to do this for every record

Any guidance appreciated!

Re: Create a Column using DAX that flags if a row existed in the previous month based on a ID Field

Anonymous — Mon, 15 Jul 2019 18:08:08 GMT

What is the data type of the Month column?

Best

Darek

Re: Create a Column using DAX that flags if a row existed in the previous month based on a ID Field

Anonymous — Mon, 22 Jul 2019 08:15:58 GMT

Date/Time

Re: Create a Column using DAX that flags if a row existed in the previous month based on a ID Field

Anonymous — Tue, 23 Jul 2019 18:08:22 GMT

[Existed In Prev Month] = -- calculated column
-- T[Month] must be the first day of the month
var __currentMonth = T[Month]
var __currentID = T[ID]
-- This moves the MONTH to be the first day
-- of the prior month.
var __prevMonth = EDATE ( __currentMonth, -1 )
var __existedInPrevMonth =
	not isempty(
		filter(
			T,
			T[ID] = __currentID,
			T[Month] = __prevMonth 
		)
	)
return
	__existedInPrevMonth

Best

Darek