Line chart between two dates

[Gabriel_Pedri]

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:

[MNedix]

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;5

If 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,

[Gabriel_Pedri]

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]
)

[MNedix]

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.

[Gabriel_Pedri]

@MNedix 

I can achieve the same result as well.

But when I insert 'Description', I get this output:

[MNedix · Accepted solution]

@Gabriel_Pedri 

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.

[Gabriel_Pedri]

@MNedix 

I think this is the best proposal, so I'll create a separate database just for installments.

Thank you very much for your attention!

[Gabriel_Pedri]

@MNedix 

Could you provide me with the file?

Would it be possible to have an opening for debts?

Example, one line for each debt

• Kitchen Set
• Python Course
• Annual Health Plan
• Wireless Headphones
• Smartphone
  
  

And I don't want the total amount of that debt, but rather its installments throughout the year.

For example, if I acquire a debt of 200 and split it into 5 installments, I'll have to pay 40 over the course of 5 months.

I want to display this graphically for each debt.

[Gabriel_Pedri]

@MNedix 

"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.

[Anonymous]

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.

[Gabriel_Pedri]

@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