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...
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)
    )

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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)
)