Forum Discussion

Gabriel_Pedri's avatar
Gabriel_Pedri
Resolver I
2 years ago
Solved

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 l...
Need Help
Tutorial Requests
  • MNedix's avatar
    MNedix
    2 years ago

    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.

     

MNedix's avatar
MNedix
Solution Sage
2 years ago

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's avatar
Gabriel_Pedri
Resolver I
2 years ago

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's avatar
    MNedix
    Solution Sage
    2 years ago

    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's avatar
      Gabriel_Pedri
      Resolver I
      2 years ago

      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's avatar
      Gabriel_Pedri
      Resolver I
      2 years ago

      MNedix 

       

      I can achieve the same result as well.

       

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

      • MNedix's avatar
        MNedix
        Solution Sage
        2 years ago

        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.