Calculated Max Date Column Filtered by Category

[Vnidias]

Good morning i´m trying to create a max column to set the following condition:

if the max date is the max date from the store, put a flag Y

In a measure i did this and worked well, but the user need use this flag as filter.

the measure created:

Flag Visited :=

VAR datemax = CALCULATE(MAX('Date'[Date]),FILTER(ALL('Execution Audit KPI'),'Execution Audit KPI'[Audit Store SK] = SELECTEDVALUE('Execution Audit KPI'[Audit Store SK])))

VAR flag = IF(datemax = SELECTEDVALUE('Date'[Date]) ,"Y","N")

return

flag

What i need: create the same condition done by measure but by a calculated column

My try:

flagnova =

var maxdate = CALCULATE(

MAXX(

SUMMARIZE('exec audit','exec audit'[Audit Store SK]),MAX('exec audit'[Start Date SK])

),ALLEXCEPT('exec audit','exec audit'[Audit Store SK]))

return  

IF(maxdate = 'exec audit'[Start Date SK] ,"Y","N")

Results from my try

It almost worked, but instead to do the max date per month he did the max date in a day level.

Table Model example:

Could you guys help me with this ?

Att.

[tamerj1]

Hi @Vnidias 

please try

flagnova =
VAR maxdate =
CALCULATE (
MAX ( 'exec audit'[Date] ),
ALLEXCEPT ( 'exec audit', 'exec audit'[Audit Store SK] )
)
RETURN
IF ( 'exec audit'[Start Date SK] = maxdate, "Y", "N" )

[Vnidias]

At the following moment this filtered did partially correct.

When i have two dates in a same month works.

ex:
2022-01-10, 

2022-01-15

He marks correct.

But when the store has more months visited like:

2022-01-10, 

2022-01-15,

2022-02-20

Doesnt work properly.

He will put 

2022-01-10, N

2022-01-15, N 

2022-02-20, Y

What he should do for the same store ID

2022-01-10, N

2022-01-15, Y

2022-02-20, Y

[tamerj1 · Accepted solution]

Hi @Vnidias 
In this case the easiest appraoch is to first create a YearMonth YYYMM integer data type column

YearMonth = YEAR ( 'exec audit'[Date] ) * 100 + MONTH ( 'exec audit'[Date] )

Then the formula would be

flagnova =
VAR maxdate =
    CALCULATE (
        MAX ( 'exec audit'[Date] ),
        ALLEXCEPT (
            'exec audit',
            'exec audit'[Audit Store SK],
            'exec audit'[YearMonth]
        )
    )
RETURN
    IF ( 'exec audit'[Start Date SK] = maxdate, "Y", "N" )