Filter based on date slicer

[benjellounm]

Hello, 

I have this complicated task and I hope someone can help me, I have this table where I have "Company name" & "Award Date", and I want to add a column where it shows which date is the latest award date for that specific vendor and that based on date slicer

table with all data

Company name	Award Date
xyz	9/1/2020
xyz	10/2/2020
xyz	11/4/2020
xyz	1/2/2021

add isLatest column with all data

Company name	Award Date	isLatest
xyz	9/1/2020	No
xyz	10/2/2020	No
xyz	11/4/2020	No
xyz	1/2/2021	Yes

using the date slicer between 9/1/2020 and 10/2/2020

Company name	Award Date	latest
xyz	9/1/2020	No
xyz	10/2/2020	Yes

please help, 

Thanks in advance. 

[v-kelly-msft · Accepted solution]

Hi  @benjellounm ,

First create a calendar table as below:

Calendar = CALENDAR(MIN('Table'[Award Date]),MAX('Table'[Award Date]))

Then create a measure as below:

Measure =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Award Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Award Date] >= MINX ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] )
                && 'Table'[Award Date] <= MAXX ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] )
                && 'Table'[Company name] = MAX ( 'Table'[Company name] )
        )
    )
RETURN
    IF (
        MAX ( 'Table'[Award Date] )
            > MAXX ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] ),
        BLANK (),
        IF ( MAX ( 'Table'[Award Date] ) = _maxdate, "Yes", "No" )
    )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[parry2k]

@benjellounm not sure why, see the attached file and the award table. There are other tables in the file, just ignore those.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@benjellounm add a measure using following expression and it will take care of it:

isLatest = 
VAR __awardDate = MAX ( Award[Award Date] )
VAR __maxDate = 
CALCULATE ( 
     MAX ( Award[Award Date] ), 
     TOPN ( 1, ALLSELECTED ( Award ),  CALCULATE ( MAX ( Award[Award Date] ), VALUES ( Award[Company name] ) ) , DESC )
) 
RETURN
IF ( __awardDate = __maxDate, "Yes", "No" )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[benjellounm]

Hello - Thank you for the help but it didn't work, I got all "Yes" for all the dates

Company name	Award Date	isLatest
xyz	9/1/2020	Yes
xyz	10/2/2020	Yes
xyz	11/4/2020	Yes
xyz	1/2/2021	Yes

[amitchandak]

@benjellounm , You can create measure

If( max(Table[Date]) = calculate(max(Table[Date]), filter(allselected(Table), Table[Date] =max(Table[Date]))) , "Yes", "No")

Column does not take slicer values , so you have to use a measure