Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.

CALCULATE
COUNT
Date
In
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , 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)
    )

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , 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)
    )