How to show the latest date within current selection in Power BI

[GKJARC]

Hi everyone,

In Power BI I'm trying to show the earliest date per ID. 

As an example, if the ValidFrom date slicer is set from 1-5-2020 to 1-12-2020, and a slicer for ID set to ID="1", the table visual should show only ID 1 with date 1-5-2020.

Sample data:

ID     ValidFrom 
1       1-1-2020

1       1-5-2020

2       1-1-2019

2       1-1-2019

3       1-1-2019

What would be a way to achieve this in Power BI?
Thanks!

[v-janeyg-msft · Accepted solution]

Hi, @GKJARC 

According to your description, I think you can easily create a measure then use it in filter pane to filter the desired results.

Like this:

Measure =
VAR a =
    MINX (
        FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) ),
        [ValidFrom ]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Table'[ValidFrom ] )
            IN DISTINCT ( 'Table 2'[Date] )
                && SELECTEDVALUE ( 'Table'[ValidFrom ] ) = a,
        1,
        0
    )

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[Jihwan_Kim]

hi, @GKJARC 

Please check the below picture and the sample pbix file's link down below.

Earliest Valid Date =
IF (
ISFILTERED ( IDs[ID] ),
CALCULATE (
MIN ( 'Table'[ValidFrom] ),
FILTER ( ALLSELECTED ( Dates ), Dates[Date] >= MIN ( Dates[Date] ) )
)
)

https://www.dropbox.com/s/5r5fidugk3q09wm/gkjarc.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[vanessafvg]

what is the table structure and relationships? or is this one table?

earliestdate =
VAR selectedid =
    SELECTEDVALUE ( id )
RETURN
    CALCULATE ( MIN ( validfrom ), id = selectedid )

[amitchandak]

@GKJARC , Try a measure like

measure =
var _min = minx(allselected(Table), Table[ValidFrom])
return
calculate(min(Table[ID]), filter(Table, Table[ValidFrom] =_min))

Or plot this with ID column in visual

measure =
var _min = minx(allselected(Table), Table[ValidFrom])
return
calculate(count(Table[ID]), filter(Table, Table[ValidFrom] =_min))