Filtering dataset to show most updated data depending on date slicer

[pezakas]

Hello community!

Here is a small example of my dataset and below i post what i am expecting as a solution. Please dont confuse the date in date slicer with the effective date. The date in date slicer will be used from a calendar table:

Dataset:

Effective Date	ID	Status
8/8/2023	001	Pending
8/8/2023	002	Resolved
15/8/2023	003	Pending
22/8/2023	001	Resolved
22/8/2023	003	Resolved
22/8/2023	004	Pending

Date slicer selection 8/8/2023:

			8/8/2023
Effective Date	ID	Status	Current flag
8/8/2023	001	Pending	1
8/8/2023	002	Resolved	1
15/8/2023	003	Pending	0
22/8/2023	001	Resolved	0
22/8/2023	003	Resolved	0
22/8/2023	004	Pending	0

Date slicer selection 15/8/2023:

			15/8/2023
Effective Date	ID	Status	Current flag
8/8/2023	001	Pending	1
8/8/2023	002	Resolved	1
15/8/2023	003	Pending	1
22/8/2023	001	Resolved	0
22/8/2023	003	Resolved	0
22/8/2023	004	Pending	0

Date slicer selection 22/8/2023:

			22/8/2023
Effective Date	ID	Status	Current flag
8/8/2023	001	Pending	0
8/8/2023	002	Resolved	1
15/8/2023	003	Pending	0
22/8/2023	001	Resolved	1
22/8/2023	003	Resolved	1
22/8/2023	004	Pending	1

[Anonymous · Accepted solution]

Hi @pezakas ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Current flag = 

var _a=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Effective Date]<=SELECTEDVALUE('calendar table'[Date]) && 'Table'[ID]=MAX('Table'[ID])),[Effective Date])
return IF(_a <=SELECTEDVALUE('calendar table'[Date]) && _a= MAX('Table'[Effective Date]),1,0)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[amitchandak]

@pezakas , refer this example code

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...