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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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!

 

3 REPLIES 3
Anonymous
Not applicable

[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

Anonymous
Not applicable

What is the data type of the Month column?

 

Best

Darek

Anonymous
Not applicable

Date/Time

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors