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've spent quite a lot of time trying to figure this out. I'm hoping someone can help.
I have a table with the following columns:
LoanID Date State
003 05/05/2021 InArrears
003 06/05/2021 Cured
003 11/05/2021 Settled
Every loan has a row for each date until it settles.
I also have a calendar table with Date that I'm using as a filter to display number of accounts that have entered arrears in the time period selected in the filter. I need to create a measure that shows the number of loans in specific state at the end of the selected period.
I tried:
Solved! Go to Solution.
Hi @mariyahd ,
Check the measures.
Measure 1 =
var _maxdate = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Loan ID]),'Table'[Date]<=MAX('date'[date])))
return
CALCULATE(MAX('Table'[State]),FILTER('Table','Table'[Date]=_maxdate))
Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[Loan ID]),FILTER('Table','Table'[State]=[Measure 1]))+0
Measure 1 is to get the latest status for each id according to the slicer date.
Measure 2 is to count the ids for each status.
Best Regards,
Jay
Hi @mariyahd ,
Check the measures.
Measure 1 =
var _maxdate = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Loan ID]),'Table'[Date]<=MAX('date'[date])))
return
CALCULATE(MAX('Table'[State]),FILTER('Table','Table'[Date]=_maxdate))
Measure 2 = CALCULATE(DISTINCTCOUNT('Table'[Loan ID]),FILTER('Table','Table'[State]=[Measure 1]))+0
Measure 1 is to get the latest status for each id according to the slicer date.
Measure 2 is to count the ids for each status.
Best Regards,
Jay
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 |