Forum Discussion

KeithC's avatar
KeithC
Frequent Visitor
3 years ago
Solved

Return value associated with latest date

Hi All   I am still relatively new in my DAX journey and looking for some assitance. I have a snippet of a table below and I have tried to create a calculated measure that   1. sum of the count  ...
  • johnt75's avatar
    johnt75
    3 years ago

    This will ignore any filters from anywhere

    Prev CMI Count =
VAR MaxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR PrevDate =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate, REMOVEFILTERS () )
VAR Result =
    CALCULATE ( [Count total], REMOVEFILTERS (), 'Table'[Date] = PrevDate )
RETURN
    Result
johnt75's avatar
johnt75
Super User
3 years ago

Try

Prev CMI Count =
VAR MaxDate =
    MAX ( 'Table'[Date] )
VAR PrevDate =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate )
VAR Result =
    CALCULATE ( [Count total], REMOVEFILTERS ( 'Table' ), 'Table'[Date] = PrevDate )
RETURN
    Result
KeithC's avatar
KeithC
Frequent Visitor
3 years ago

Thanks johnt75 

 

Have tried this to get the max and the second max and have obtained the correct counts but these change if I have any slicers on my dashboard. Can the DAX formula be modified so the result is constant?

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    This will ignore any filters from anywhere

    Prev CMI Count =
VAR MaxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS () )
VAR PrevDate =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Date] < MaxDate, REMOVEFILTERS () )
VAR Result =
    CALCULATE ( [Count total], REMOVEFILTERS (), 'Table'[Date] = PrevDate )
RETURN
    Result
    • KeithC's avatar
      KeithC
      Frequent Visitor
      3 years ago

      Excellent JohnT this has now worked and above is understood. Thanks again!