Maximum or last value of CONCATENATEX variable (MAX does not work)

[DM_95]

Hi everyone,

Maybe there is a simple answer to my question, but does anybode know how to get the maximum or last value of a CONCATENATEX variable. Unfortunately, MAX(_Concat) does not work (see formula below). The goal is to get the maximum value of slicer 'Month' e.g. When Month 1 till 9 is selected, it needs to return 9.

MaxSelectedMonth=

VAR _Concat = CONCATENATEX(VALUES('Invoice Date'[MonthInYear]), 'Invoice Date'[MonthInYear], ", ")

RETURN

MAX(_Concat)

Thanks in advance!

[PVO3 · Accepted solution]

Based on assumptions whatever your data looks like. And assuming 'Invoice Date' is your data table, not your fact table.

Easiest solution - create 2 columns in 'Invoice Date'
- [Column1] A concatenaded column of month/year

- [Column2] A numeric value of moth/year (like januari 2021 = 202201)

Then create a measure 

Measure =

VAR _max = MAX([Column2])

RETURN

CALCULATE(

   MIN([Column1]),

   [Column2] = _max

)