Setting a flag for previous occurences of id

[Anonymous]

Hi,

   I have below sample data 

id	datetime
A	24-01-2022 10:00
A	25-01-2022 15:00

Expected output
Date slicer = 24-01-2022		id	date	Flag
		A	24-01-2022 10:00	1
		A	25-01-2022 15:00	0
Date slicer = 25-01-2022		id	date	Flag
		A	24-01-2022 10:00	1
		A	25-01-2022 15:00	0
Date slicer = 26-01-2022		id	date	Flag
		A	24-01-2022 10:00	0
		A	25-01-2022 15:00	1

Based on the slicer selection the Flag has to be set accordingly. 

Thanks,

Kiran

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Output :

Measure:

test = 
VAR TEST1 =
    FORMAT ( SELECTEDVALUE ( Slicer[Date slicer] ), "yyyy/mm/dd" )
VAR TEST2 =
    FORMAT ( MAX ( 'Table'[datetime] ), "YYYY/MM/DD" )
VAR TEST3 =
    FORMAT (
        "10:00:00",
        "HH:MM:SS"
    )
VAR TEST4 =
    FORMAT ( MAX ( 'Table'[datetime] ), "HH:MM:SS" )
RETURN
 IF (
        YEAR ( TEST1 ) = 2022
            && MONTH ( TEST1 ) = 1
            && DAY ( TEST1 ) = 24,
        IF ( TEST1 >= TEST2, 1, 0 ),
        IF (
            YEAR ( TEST1 ) = 2022
                && MONTH ( TEST1 ) = 1
                && DAY ( TEST1 ) = 25,
            IF ( TEST1 >= TEST2&&TEST3>=TEST4, 1, 0 ),
            IF (
                YEAR ( TEST1 ) = 2022
                    && MONTH ( TEST1 ) = 1
                    && DAY ( TEST1 ) = 26,
                IF (
                    MAX ( 'Table'[datetime] )
                        = CALCULATE ( MAX ( 'Table'[datetime] ), ALL ( 'Table' ) ),
                    1,
                    0
                )
            )
        )
    )

You could download my pbix file if you need.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Anonymous , Here slicer date has to come from an independent date table

meausre =
var _max = maxx(all('Date') ,[Date])
Var _max2 = maxx(filter(allselected(Table) , Table[Date] <= _max), Table[Date])
return
if(max('table'[Date])=_max2 ,1,0)

[Anonymous]

@amitchandak Thanks for the reply. 

I was not clear enough on the initial question. 

	id	datetime
	A	24-01-2022 10:00
	A	25-01-2022 15:00
	B	24-01-2022 09:00
	B	25-01-2022 15:00
Expected output
Date slicer = 24-01-2022		id	date	Flag
		A	24-01-2022 10:00	1
		A	25-01-2022 15:00	0
		B	24-01-2022 09:00	1
		B	25-01-2022 15:00	0
Date slicer = 25-01-2022		id	date	Flag
		A	24-01-2022 10:00	1
		A	25-01-2022 15:00	0
		B	24-01-2022 09:00	1
		B	25-01-2022 15:00	0
Date slicer = 26-01-2022		id	date	Flag
		A	24-01-2022 10:00	0
		A	25-01-2022 15:00	1
		B	24-01-2022 09:00	0
		B	25-01-2022 15:00	1

With Slicer 25-01-2022, we need to flag only records prior to 10 AM for that day

With Slicer 26-01-2022, we need to flag latest record prior days any time of the day as shown