Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter on record that has an differend date

Hi all,

 

I was wondering if there is an way to achieve to following:

- In my raw data I've got several customers, statusses and open_dates
- In the reports I want to select one of the customers and that the report automatically looks for the first date that is different than 'Closed' and take the firtst date of that month.

 

I've created some screenshots to explain.

The raw data:

This is what I want to receive, a report for customer A but 
The first date that hasn't status <> Closed = 30/04/2020 so the report should take 01/04/2020 as startdate for filtering.

Example for customer B:

First date with status <> Closed 15/09/2020. So the filter should take 01/09/2020 as startdate

Exampel for customer C:

First date <> Closes = 19/01/2020. Filtering needs to be done on open_date = 01/01/2020 (so the record from 03/01/2020) should also be visible in the report.

 

I hope someone can help me on this, it would help me a LOT!

 

thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Create a measure as below then add it to visual filter.

    Measure = 
var _min = CALCULATE(MIN('Table'[open date]),FILTER(ALLEXCEPT('Table','Table'[customer]),'Table'[status]<>"closed"))
return
IF(SELECTEDVALUE('Table'[open date])>=_min,1,0)

     

    Best Regards,

    Jay

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    Do you want this calculation to be dinamic based on the selection on of other fields on top of the customer or is it only the customer?

     

    You could create an auxiliar table with the customer and the first date that is not closed and then use it has your filtering.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, 

       

      sorry about the delayed response.
      It's only on the Customer that I need the first date that is different than 'Closed'.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Create a measure as below then add it to visual filter.

    Measure = 
var _min = CALCULATE(MIN('Table'[open date]),FILTER(ALLEXCEPT('Table','Table'[customer]),'Table'[status]<>"closed"))
return
IF(SELECTEDVALUE('Table'[open date])>=_min,1,0)

     

    Best Regards,

    Jay