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.
Hello,
I need a way to find last bills sent to a customer that add up to his total debt as well as the oldest bill he has not payed.
Example
If debt for Customer AAA is 500, I need to get list of last 2 bils: "003, 004" and date 06.01.2023.
If debt for a Customer AAA is 600, this list should also include secound bill, so result would be "002, 003, 004" and date 04.01.2023.
Would really appreciate a help with this
Solved! Go to Solution.
Hi,
Here is one way to do this:
1. add customer and calendar tables
2. add relationships:
3. dax:
tests:
The test works as expected.
Proud to be a Super User!
Hi,
Here is one way to do this:
1. add customer and calendar tables
2. add relationships:
3. dax:
tests:
The test works as expected.
Proud to be a Super User!
Thank you for help!
My end result was getting date of the oldest unpayed bill and list of unpayed bills next to customer in my customer table. Had to modify measures to archive that, but idea stayed the same as you suggested.
I had measure that calculates sum of all the future bills including selected one
future_bill_sum =
VAR date_k =
MAX ( BILLS[DATE] )
RETURN
CALCULATE (
SUM ( BILLS[VALUE] ),
ALL ( BILLS[ID] ),
ALL ( BILLS[DATE] ),
FILTER ( ALL ( DIM_CALENDAR ), DIM_CALENDAR[DATE] >= date_k )
)
Then I had measure to find the oldest unpayed bill date
oldest_bill_date =
VAR debt =
MAX ( DIM_CUSTOMERS[Debt] )
RETURN
CALCULATE (
MAXX (
FILTER (
SUMMARIZE ( BILLS, BILLS[ID], BILLS[DATE], "current_sum", [future_bill_sum] ),
[current_sum] >= debt
),
BILLS[DATE]
)
)
And another mesure to get the list of unpayed bills
list_of_bills =
VAR date_from = [oldest_bill_date]
RETURN
CONCATENATEX (
FILTER ( SUMMARIZE ( BILLS, BILLS[ID], BILLS[DATE] ), BILLS[DATE] >= date_from ),
BILLS[ID],
", "
)
In the end I got this result:
User | Count |
---|---|
70 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |