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.

     

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.

       

      • Gabriel_Pedri's avatar
        Gabriel_Pedri
        Resolver I
        2 years ago

        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!