Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering a table to a specific date

Hello everyone.

 

I'm having a issue filtering data from a table. But first of all I put you a little bit in situation.

 

I have the next table wich storages computers or devices with two dates columns: from and to. This two columns tell us when a computers has been assgined and until when.

 

As you can see, the date_to has values with year 2199. This is because this records is still active. The record 20 (device number 33) was used from 24/05/19 to 27/05/19 and used again in the same day and still active.

 

With ths data in Power BI I'd like do two things:

  • to pick up a single date, for example today or the current month, and show the active records.
  • to show, if it's posible, a grapth with the active records in a specific date: this month, last year, etc..

I tried it for several ways but I don't get the expected result.

 

Thank you in advance.

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may try to create measures and use it in visual level filter.For example:

     

    Selected_date =
IF (
    MAX ( Table1[date_from] ) <= SELECTEDVALUE ( 'Date'[Date] )
        && MAX ( Table1[date_to] ) >= SELECTEDVALUE ( 'Date'[Date] ),
    1
)

    Selected_month = IF(MONTH(MAX(Table1[date_from]))<=SELECTEDVALUE('Date'[Month])&&MONTH(MAX(Table1[date_to]))>=SELECTEDVALUE('Date'[Month]),1)
    Selected_lastyear = var selected_Date=SELECTEDVALUE('Date'[Date]) return IF(MAX(Table1[date_from])<= DATE(YEAR(selected_Date)-1,MONTH(selected_Date),DAY(selected_Date))&&MAX(Table1[date_to])>=DATE(YEAR(selected_Date)-1,MONTH(selected_Date),DAY(selected_Date)),1)

     

    Regards,

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous 

    You may try to create measures and use it in visual level filter.For example:

     

    Selected_date =
IF (
    MAX ( Table1[date_from] ) <= SELECTEDVALUE ( 'Date'[Date] )
        && MAX ( Table1[date_to] ) >= SELECTEDVALUE ( 'Date'[Date] ),
    1
)

    Selected_month = IF(MONTH(MAX(Table1[date_from]))<=SELECTEDVALUE('Date'[Month])&&MONTH(MAX(Table1[date_to]))>=SELECTEDVALUE('Date'[Month]),1)
    Selected_lastyear = var selected_Date=SELECTEDVALUE('Date'[Date]) return IF(MAX(Table1[date_from])<= DATE(YEAR(selected_Date)-1,MONTH(selected_Date),DAY(selected_Date))&&MAX(Table1[date_to])>=DATE(YEAR(selected_Date)-1,MONTH(selected_Date),DAY(selected_Date)),1)

     

    Regards,