Forum Discussion

pezakas's avatar
pezakas
Icon for Helper I rankHelper I
2 years ago
Solved

Filtering dataset to show most updated data depending on date slicer

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 DateIDStatus
8/8/2023001Pending
8/8/2023002Resolved
15/8/2023003Pending
22/8/2023001Resolved
22/8/2023003Resolved
22/8/2023004Pending

 

Date slicer selection 8/8/2023:

   8/8/2023
Effective DateIDStatusCurrent flag
8/8/2023001Pending1
8/8/2023002Resolved1
15/8/2023003Pending0
22/8/2023001Resolved0
22/8/2023003Resolved0
22/8/2023004Pending0

 

Date slicer selection 15/8/2023:

   15/8/2023
Effective DateIDStatusCurrent flag
8/8/2023001Pending1
8/8/2023002Resolved1
15/8/2023003Pending1
22/8/2023001Resolved0
22/8/2023003Resolved0
22/8/2023004Pending0

 

Date slicer selection 22/8/2023:

   22/8/2023
Effective DateIDStatusCurrent flag
8/8/2023001Pending0
8/8/2023002Resolved1
15/8/2023003Pending0
22/8/2023001Resolved1
22/8/2023003Resolved1
22/8/2023004Pending1
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

2 Replies