Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @jorismo ,
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
Hi @jorismo ,
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
Hi @jorismo ,
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
sorry about the delayed response.
It's only on the Customer that I need the first date that is different than 'Closed'.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |