Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Ratpay | Invoice | Date |
600 | 1234 | 5/1/2021 |
500 | 1234 | 5/8/2021 |
400 | 1234 | 5/15/2021 |
300 | 1234 | 5/22/2021 |
200 | 1234 | 5/29/2021 |
100 | 1234 | 6/5/2021 |
350 | 5678 | 5/3/2021 |
250 | 5678 | 5/10/2021 |
150 | 5678 | 5/17/2021 |
50 | 5678 | 5/24/2021 |
Solved! Go to Solution.
My measure, as I said, works perfectly OK.
[Total Outstanding] =
var LastVisibleDate = MAX( 'Date'[Date] )
var Result =
CALCULATE(
SUMX(
DISTINCT( Invoices[InvoiceNumber] ),
CALCULATE(
MAXX(
TOPN(1,
Invoices,
Invoices[Date],
DESC
),
Invoices[Ratpay]
)
)
),
'Date'[Date] <= LastVisibleDate,
// This last line is redundant if 'Date'
// has been marked as a date table in
// the model.
REMOVEFILTERS( 'Date' )
)
return
Result
@Anonymous, I would kindly recommend that you invest some time into understanding what ALLSELECTED is for. Here's something that should help you understand: https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/
You should also remember that fields from fact tables should never be used for slicing and dicing. Dimensions and only they are suitable for this purpose.
Ratpay | Invoice | Date |
600 | 1234 | 5/1/2021 |
500 | 1234 | 5/8/2021 |
400 | 1234 | 5/15/2021 |
300 | 1234 | 5/22/2021 |
200 | 1234 | 5/29/2021 |
100 | 1234 | 6/5/2021 |
350 | 5678 | 5/3/2021 |
250 | 5678 | 5/10/2021 |
150 | 5678 | 5/17/2021 |
50 | 5678 | 5/24/2021 |
My formula gives you everything you want if you slice your data properly.
User | Count |
---|---|
53 | |
45 | |
19 | |
16 | |
14 |
User | Count |
---|---|
108 | |
56 | |
29 | |
21 | |
17 |