Forum Discussion
Line chart between two dates
I want to create a chart that displays the monthly installments of my credit card purchases.
Essentially, I have a dataset with columns for Purchase Date and PaymentDate.
I want to create a line chart that shows this calculation:
Account Payable = DIVIDE([Outstanding Debts], SUM(factBankStatement[installments
]))
Outstanding Debts returns my expenses:
Outstanding Debts =
SUMX(
FILTER(
factBankStatement,
factBankStatement[PaymentMethod] = "Credit" &&
factBankStatement[PurchaseDate] >= MIN(dimCalendar[Date])
),
factBankStatement[Amount]
)
Expected Outcome (with fictitious values):
However, the start and end of the line should be based on the PurchaseDate and PaymentDate columns.
CSV Database (with fictitious values)
BankStatement_2024
PurchaseDate;PaymentDate;Description;PaymentMethod;Amount;Balance;Installments
01/15/2024;01/15/2024;Gift;Debit;-25;4975;1
01/20/2024;01/20/2024;Investment;Cash;-3000;1975;1
01/20/2024;01/20/2024;Electronics Purchase;Cash;-4500;-2525;1
01/25/2024;01/25/2024;Grocery Store;Debit;-300;-2825;1
01/25/2024;01/25/2024;Pharmacy;Debit;-150;-2975;1
02/01/2024;02/01/2024;Salary;Cash;5000;2025;1
02/01/2024;02/01/2024;Bonus;Cash;1500;3525;1
02/05/2024;02/05/2024;Coffee;Debit;-30;3495;1
02/05/2024;02/05/2024;Snack;Debit;-15;3480;1
02/10/2024;02/10/2024;Rent;Cash;1200;4680;1
02/10/2024;02/10/2024;Book Purchase;Cash;-600;4080;1
02/15/2024;02/15/2024;Freelance Work;Cash;600;4680;1
02/15/2024;02/15/2024;Gift;Debit;-90;4590;1
02/20/2024;02/20/2024;Investment;Cash;-1500;3090;1
02/20/2024;02/20/2024;Clothing Purchase;Cash;-900;2190;1
02/25/2024;02/25/2024;Grocery Store;Debit;-450;1740;1
02/25/2024;02/25/2024;Pharmacy;Debit;-225;1515;1
03/01/2024;03/01/2024;Salary;Cash;5000;6515;1
03/01/2024;03/01/2024;Bonus;Cash;2000;8515;1
03/05/2024;03/05/2024;Leisure;Debit;-210;8305;1
03/05/2024;03/05/2024;Taxi;Debit;-90;8215;1
03/10/2024;03/10/2024;Rent;Cash;1200;9415;1
03/10/2024;03/10/2024;Electronics Purchase;Cash;-2400;7015;1
03/15/2024;03/15/2024;Freelance Work;Cash;700;7715;1
03/15/2024;03/15/2024;Gift;Debit;-105;7610;1
03/20/2024;03/20/2024;Investment;Cash;-1800;5810;1
03/20/2024;03/20/2024;Office Supplies Purchase;Cash;-600;5210;1
03/25/2024;03/25/2024;Grocery Store;Debit;-360;4850;1
03/25/2024;03/25/2024;Pharmacy;Debit;-180;4670;1
04/01/2024;04/01/2024;Salary;Cash;5000;9670;1
04/01/2024;04/01/2024;Bonus;Cash;2500;12170;1
04/05/2024;04/05/2024;Transportation;Debit;-60;12110;1
04/05/2024;04/05/2024;Snack;Debit;-30;12080;1
04/10/2024;04/10/2024;Rent;Cash;1200;13280;1
04/10/2024;04/10/2024;Clothing Purchase;Cash;-450;12830;1
04/15/2024;04/15/2024;Smartphone;Credit;-1200;11630;10
04/15/2024;04/15/2024;Freelance Work;Cash;900;12530;1
04/15/2024;04/15/2024;Gift;Debit;-120;12410;1
04/20/2024;04/20/2024;Investment;Cash;-2100;10310;1
04/20/2024;04/20/2024;Furniture Purchase;Cash;-1200;9110;1
04/25/2024;04/25/2024;Grocery Store;Debit;-540;8570;1
04/25/2024;04/25/2024;Pharmacy;Debit;-270;8300;1
05/01/2024;05/01/2024;Salary;Cash;5000;13300;1
05/01/2024;05/01/2024;Bonus;Cash;3000;16300;1
05/02/2024;05/11/2024;Wireless Headphones;Credit;-450;15850;5
05/05/2024;05/05/2024;Transportation;Debit;-90;15760;1
05/05/2024;05/05/2024;Snack;Debit;-45;15715;1
05/10/2024;05/10/2024;Rent;Cash;1200;16915;1
05/10/2024;05/10/2024;Electronics Purchase;Cash;-3000;13915;1
05/15/2024;05/15/2024;Freelance Work;Cash;1000;14915;1
05/15/2024;05/15/2024;Gift;Debit;-150;14765;1
05/20/2024;05/20/2024;Annual Health Plan;Credit;-530;14235;10
05/20/2024;05/20/2024;Investment;Cash;-2400;11835;1
05/20/2024;05/20/2024;Clothing Purchase;Cash;-600;11235;1
05/25/2024;05/25/2024;Grocery Store;Debit;-600;10635;1
05/25/2024;05/25/2024;Pharmacy;Debit;-300;10335;1
06/01/2024;06/01/2024;Salary;Cash;5000;15335;1
06/01/2024;06/01/2024;Bonus;Cash;3500;18835;1
06/05/2024;06/05/2024;Transportation;Debit;-120;18715;1
06/05/2024;06/05/2024;Snack;Debit;-60;18655;1
06/10/2024;06/10/2024;Python Course;Credit;-1500;17155;6
06/10/2024;06/10/2024;Rent;Cash;1200;18355;1
06/10/2024;06/10/2024;Furniture Purchase;Cash;-2400;15955;1
06/15/2024;06/15/2024;Freelance Work;Cash;1100;17055;1
06/15/2024;06/15/2024;Gift;Debit;-180;16875;1
06/20/2024;06/20/2024;Investment;Cash;-2700;14175;1
06/20/2024;06/20/2024;Electronics Purchase;Cash;-900;13275;1
06/25/2024;06/25/2024;Grocery Store;Debit;-750;12525;1
06/25/2024;06/25/2024;Pharmacy;Debit;-375;12150;1
06/28/2024;06/28/2024;Kitchen Set;Credit;-1300;10850;4
07/01/2024;07/01/2024;Salary;Cash;5000;15850;1
07/01/2024;07/01/2024;Bonus;Cash;4000;19850;1
07/05/2024;07/05/2024;Transportation;Debit;-90;19760;1
07/05/2024;07/05/2024;Snack;Debit;-45;19715;1
07/10/2024;07/10/2024;Rent;Cash;1200;20915;1
07/15/2024;07/15/2024;Freelance Work;Cash;1200;22115;1
07/15/2024;07/15/2024;Gift;Debit;-210;21905;1
07/20/2024;07/20/2024;Investment;Cash;-3000;18905;1
07/20/2024;07/20/2024;Clothing Purchase;Cash;-750;18155;1
07/25/2024;07/25/2024;Grocery Store;Debit;-900;17255;1
07/25/2024;07/25/2024;Pharmacy;Debit;-450;16805;1
08/01/2024;08/01/2024;Salary;Cash;5000;21805;1
08/01/2024;08/01/2024;Bonus;Cash;5000;26805;1
08/05/2024;08/05/2024;Transportation;Debit;-120;26685;1
08/05/2024;08/05/2024;Snack;Debit;-60;26625;1
08/10/2024;08/10/2024;Rent;Cash;1200;27825;1
08/15/2024;08/15/2024;Freelance Work;Cash;1300;29125;1
08/15/2024;08/15/2024;Gift;Debit;-240;28885;1
08/20/2024;08/20/2024;Investment;Cash;-3600;25285;1
08/20/2024;08/20/2024;Electronics Purchase;Cash;-1200;24085;1
08/25/2024;08/25/2024;Grocery Store;Debit;-1050;23035;1
08/25/2024;08/25/2024;Pharmacy;Debit;-525;22510;1
09/01/2024;09/01/2024;Salary;Cash;5000;27510;1
09/01/2024;09/01/2024;Bonus;Cash;5500;33010;1
09/05/2024;09/05/2024;Transportation;Debit;-150;32860;1
09/05/2024;09/05/2024;Snack;Debit;-75;32785;1
09/10/2024;09/10/2024;Rent;Cash;1200;33985;1
09/15/2024;09/15/2024;Freelance Work;Cash;1400;35385;1
09/15/2024;09/15/2024;Gift;Debit;-270;35115;1
09/20/2024;09/20/2024;Investment;Cash;-4500;30615;1
09/20/2024;09/20/2024;Clothing Purchase;Cash;-900;29715;1
09/25/2024;09/25/2024;Grocery Store;Debit;-1200;28515;1
09/25/2024;09/25/2024;Pharmacy;Debit;-600;27915;1
10/01/2024;10/01/2024;Salary;Cash;5000;32915;1
10/01/2024;10/01/2024;Bonus;Cash;6000;38915;1
10/05/2024;10/05/2024;Transportation;Debit;-180;38735;1
10/05/2024;10/05/2024;Snack;Debit;-90;38645;1
10/10/2024;10/10/2024;Rent;Cash;1200;39845;1
10/15/2024;10/15/2024;Freelance Work;Cash;1500;41345;1
10/15/2024;10/15/2024;Gift;Debit;-300;41045;1
10/20/2024;10/20/2024;Investment;Cash;-5400;35645;1
10/20/2024;10/20/2024;Electronics Purchase;Cash;-1500;34145;1
10/25/2024;10/25/2024;Grocery Store;Debit;-1500;32645;1
10/25/2024;10/25/2024;Pharmacy;Debit;-750;31895;1
11/01/2024;11/01/2024;Salary;Cash;5000;36895;1
11/01/2024;11/01/2024;Bonus;Cash;7000;43895;1
11/05/2024;11/05/2024;Transportation;Debit;-210;43685;1
11/05/2024;11/05/2024;Snack;Debit;-105;43580;1
11/10/2024;11/10/2024;Rent;Cash;1200;44780;1
11/15/2024;11/15/2024;Freelance Work;Cash;1600;46380;1
11/15/2024;11/15/2024;Gift;Debit;-330;46050;1
11/20/2024;11/20/2024;Investment;Cash;-6000;40050;1
11/20/2024;11/20/2024;Clothing Purchase;Cash;-1200;38850;1
11/25/2024;11/25/2024;Grocery Store;Debit;-1800;37050;1
11/25/2024;11/25/2024;Pharmacy;Debit;-900;36150;1
12/01/2024;12/01/2024;Salary;Cash;5000;41150;1
12/01/2024;12/01/2024;Bonus;Cash;8000;49150;1
12/05/2024;12/05/2024;Transportation;Debit;-240;48910;1
12/05/2024;12/05/2024;Snack;Debit;-120;48790;1
12/10/2024;12/10/2024;Rent;Cash;1200;49990;1
12/15/2024;12/15/2024;Freelance Work;Cash;1800;51790;1
12/15/2024;12/15/2024;Gift;Debit;-360;51430;1
12/20/2024;12/20/2024;Investment;Cash;-7500;43930;1
12/20/2024;12/20/2024;Electronics Purchase;Cash;-1800;42130;1
12/25/2024;12/25/2024;Grocery Store;Debit;-2100;40030;1
12/25/2024;12/25/2024;Pharmacy;Debit;-1050;38980;1
Date Base:
dimCalendar =
ADDCOLUMNS (
CALENDARAUTO(),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Month Number", MONTH([Date]),
"Weekday", WEEKDAY([Date]),
"Weekday Name", FORMAT([Date], "dddd"),
"Month Abr", FORMAT([Date], "MMM")
)
Relationship:
It is normal to have just some dots when you add the Description because the dataset is too small to draw lines (you just have singel values). I extrapolated some of your data in the dataset and made it Credit with 5 Installments each and I get the below:
I've put the PBIX file here: PBIX and the CSV here: CSV
As always, if it solved your problem then please mark it as the solution.
10 Replies
Hi Gabriel_Pedri ,
Could you please provide the pbix file? Thank you!
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Unfortunately, as a user, I am unable to share files on the forum. That's why I shared my database and the necessary steps to solve the issue.
This seems to be a recurring problem for others as well:
https://community.fabric.microsoft.com/t5/Desktop/how-to-attach-pbix-file-in-this-forum/td-p/404432
Hi,
I don't really understand what do you mean by
However, the start and end of the line should be based on the PurchaseDate and PaymentDate columns.
These are two independent events and I believe you do have different dates in your real data (as you have in the sample data)
05/02/2024;05/11/2024;Wireless Headphones;Credit;-450;15850;5If what I assume is correct then you might have two options:
1. Create two measures, one based on Purchase Date and the other on Payment Date and plot them in a line chart based on the dimDate[Date] date. You already have a relationship between [Date] and Purchase Date so you need to create the 2nd relationship. This relationship will be inactive but you can activate it when creating the measure for Payment Date by using USERELATIONSHIP at the end of your calculation.
2. Create an Outstanding Debts only if the Payment Date differs from Purchase Date (see below):
Outstanding Debt 2 = CALCULATE( SUM(Installment_BankStatement[Amount]), Installment_BankStatement[PaymentMethod] = "Credit", Installment_BankStatement[PurchaseDate] >= MIN(dimCalendar[Date]), Installment_BankStatement[PurchaseDate]<>Installment_BankStatement[PaymentDate])The do the Accounts Payable with the above measure, then plot it using the dimDate[Date].
If this answered your question please mark it as the solution.
Best,
"However, the start and end of the line should be based on the PurchaseDate and PaymentDate columns."
Sorry for not explaining clearly.
What I mean is that we need to base the line creation on this period to return its installments.
For example:
- Amount = 1000
- Installments = 5
- Installment amount = 1000 / 5 = 200
- Period = 06/20/2024 to 11/20/2024
So, we should create a line in a line chart that returns the installment values within the period from 06/20/2024 to 11/20/2024.
Regarding the measures, would it be in this model?
PaymentDate = CALCULATE( DIVIDE([Outstanding Debts], SUM(factBankStatement[Installments]), BLANK()), FILTER( ALL('dimCalendar'[Date]), 'dimCalendar'[Date] <= MAX(factBankStatement[PaymentDate]) ), USERELATIONSHIP('dimCalendar'[Date], factBankStatement[PaymentDate]) )PurchaseDate = CALCULATE( DIVIDE([Outstanding Debts], SUM(factBankStatement[Installments]), BLANK()), FILTER( ALL('dimCalendar'[Date]), 'dimCalendar'[Date] >= MAX(factBankStatement[PurchaseDate]) ) )My USERELATIONSHIP is giving the following log:
"USERELATIONSHIP function can only use the two columns references participating in relationship."
Could you please explain further why the use of two measures and what is the functionality of USERELATIONSHIP?
Regarding step 2, it returns the same value as my previous calculation.
Outstanding Debts = SUMX( FILTER( factBankStatement, factBankStatement[PaymentMethod] = "Credit" && factBankStatement[PurchaseDate] >= MIN(dimCalendar[Date]) ), factBankStatement[Amount] )Outstanding Debt 2 = CALCULATE( SUM(factBankStatement[Amount]), factBankStatement[PaymentMethod] = "Credit", factBankStatement[PurchaseDate] >= MIN(dimCalendar[Date]), factBankStatement[PurchaseDate] <> factBankStatement[PaymentDate] )I might misunderstand your problem for which I have to apologize.
First of all, in order for USERELATIONSHIP to work you have to have a realtionship in your model, even though it is inactive. It's a 1-to-2 problem, you have one Date table but two date columns so you can have only one active while the other one is inactive. For example, if you create a measure filter for PurchaseDate line items and use the Date in a slider it will work just fine. But if you create a second measure for the PaymentDate and use the same slider you will fail. That's why, in the 2nd measure you have to put USERELATIONSHIP (but you must have an inactive relationship in the model - see screenshot).
Also, perhaps I am missing something becasue I don't understand why you keep refering to the MIN(dimCalendar[Date]) and why do you divide the Credit Outstanding Debts to the overall Installments rather than only Credit Installments.
I tried to replicate your scenario with your data and this is what I got. All data is filtered for Credit, and I adjusted the Installment measures.
Screenshot 1 - all outstanding debts for Credit purchases, all time. We have 5 of them, with a total amount of 4980, with a number of Credit Installments of 35 which gives us an average of 142 Accounts Payable.
Screenshot 2 - it gives us the Credit Outstanding Debts which have a Payment date after 1 June. We have only 2, amount 2300, 10 Installments so AP is 280
Screenshot 3 - how many Credit Outstanding Debts have been created from the beginning of the year until end of May AND how many had the PaymentDate in May. We have 2, amount 980, 15 Credit Installments so AP is 65.
Screenshot 4 shows the relationship model.
I hope it helps.
