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
Pete_81
Frequent Visitor

Using a date variable in a CALCULATE and COUNTA expression?

I have these 2 DAX expressions :

ThisWeek = 
CALCULATE(
	COUNTA('MyTable'[Report Date])+0,
	'MyTable'[Report Date]
		IN { DATE(2021, 04, 02) }
)

PreviousWeek = 
CALCULATE(
	COUNTA('MyTable'[Report Date])+0,
	'MyTable'[Report Date]
		IN { DATE(2021, 03, 26) }
)

 But I want to alter them so that instead of specifing dates, they use the 2 most recent dates from the Report Dates column.

 

Merging this in, somehow:

CalcThisWeek = FORMAT(MAXX('MyTable','MyTable'[Report Date]),"YYYY/mm/dd")

 

Any ideas?

 

Thank you.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Pete_81 , Try measures like

 

measure max Date =
var _max = maxx(allselected('MyTable'),'MyTable'[Report Date])
return
CALCULATE(
COUNTA('MyTable'[Report Date])+0,
filter('MyTable', 'MyTable'[Report Date] = _max)
)

measure 2nd max Date =
var _max1 = maxx(allselected('MyTable'),'MyTable'[Report Date])
var _max = maxx(filter(allselected('MyTable'),'MyTable'[Report Date] <_max1) ,'MyTable'[Report Date])
return
CALCULATE(
COUNTA('MyTable'[Report Date])+0,
filter('MyTable', 'MyTable'[Report Date] = _max)
)

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@Pete_81 , Try measures like

 

measure max Date =
var _max = maxx(allselected('MyTable'),'MyTable'[Report Date])
return
CALCULATE(
COUNTA('MyTable'[Report Date])+0,
filter('MyTable', 'MyTable'[Report Date] = _max)
)

measure 2nd max Date =
var _max1 = maxx(allselected('MyTable'),'MyTable'[Report Date])
var _max = maxx(filter(allselected('MyTable'),'MyTable'[Report Date] <_max1) ,'MyTable'[Report Date])
return
CALCULATE(
COUNTA('MyTable'[Report Date])+0,
filter('MyTable', 'MyTable'[Report Date] = _max)
)

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