Forum Discussion

benjellounm's avatar
benjellounm
Regular Visitor
5 years ago
Solved

Filter based on date slicer

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 nameAward Date
xyz9/1/2020
xyz10/2/2020
xyz11/4/2020
xyz1/2/2021

 

add isLatest column with all data

Company nameAward DateisLatest
xyz9/1/2020No
xyz10/2/2020No
xyz11/4/2020No
xyz1/2/2021Yes

 

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

Company nameAward Datelatest
xyz9/1/2020No
xyz10/2/2020Yes

 

please help, 

 

Thanks in advance. 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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's avatar
      benjellounm
      Regular Visitor
      5 years ago

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

       

      Company nameAward DateisLatest
      xyz9/1/2020Yes
      xyz10/2/2020Yes
      xyz11/4/2020Yes
      xyz1/2/2021Yes
  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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.⚡

     

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!