Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |