Forum Discussion

Truelearner's avatar
Truelearner
Icon for Helper III rankHelper III
6 years ago
Solved

need help with dax to create table with most recent dates

I have a table like below 

datevalue
12-01-20201
18-03-20202
24-03-20203

 

I want to create the below table for my caluclations ,i would like to ignore all dates with in a month which have more than one date expect the  one with highest date 

datevalue
12-01-20201
24-03-20203

@mgwena @cham @amitchandak @Greg_Deckler @Mariusz 

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Truelearner ,

    If you want to create a measure to implement it, you could try like this:

    • Create a month column
    Month = MONTH('Table'[date])
    • Create a measure
    Measure = 
CALCULATE (
    MAX ( 'Table'[value] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Month] = MAX ( 'Table'[Month] ) )
)

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Perhaps create a month year column like:

     

    MonthYear = YEAR([date]) * 100 + MONTH([date])

     

    Table =
  SELECTCOLUMNS(
    GROUPBY(
      'Table',
      [MonthYear],
      "date",MAXX(CURRENTGROUP(),[date])
      "value",[value]
    )
    "date",[date],
    "value",[value]
  )

     

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Truelearner ,

    If you want to create a measure to implement it, you could try like this:

    • Create a month column
    Month = MONTH('Table'[date])
    • Create a measure
    Measure = 
CALCULATE (
    MAX ( 'Table'[value] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Month] = MAX ( 'Table'[Month] ) )
)