Forum Discussion

djs25uk's avatar
djs25uk
Icon for Helper I rankHelper I
6 years ago
Solved

Including Missed Payments in Data

Hi Everyone!

I was wondering if you would mind helping me by pointing me in the right direction. 

I have a data set that looks a bit like this:

 

PaymentIDPaymentStudentIDPaymentAmountPaymentDate
100012345617.0001 Jan 2020
100212345617.0001 Mar 2020
............

 

You can see that student 123456 missed a payment on 01 Feb 2020. As the payment was missed, it doesn't exist in my data set and therefore it isn't showing in my statistics. Currently my average for payments is showing £17 which looks really good but when I am able to incorporate all the missed payments, I know it will look much worse. 

Please could you help me with ideas for how I should try to include missing payments? Is this something I need to do in Transform Data or when I am actually displaying the visual?

If it is helpful to know, I am importing my data from tables in an SQL database. I also do already have a Calendar table in my Power BI data filled with the entire data range with no dates missing if that's helpful. 

Any help or advice you may have would be really greatly appreciated. I will keep searching online too!

Many thanks

Dan

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    djs25uk 

     

    Do you want to fill in the missing data or get the result?

     

    If you want to fill in the missing data, you need a table with full datetime,use lookupvalue to get the value.

    Column = LOOKUPVALUE('Table'[payment],'Table'[paymentdate],datetime[Date])+0

    If you want to get the correct result,you need to use the month and quarter column as filter.

    Measure = sum('Table'[payment])/DISTINCTCOUNT(datetime[month])

    Used distinct count to count number of month, then will be (17+17)/3. The average value will be less than what you get.

    Hope this is helpful.

     

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    djs25uk 

     

    you can use the columns in calendar table as filters.

    average = sum('Table'[payment])/DISTINCTCOUNT(datetime[month])

    Then the avearge payment will be lower than what you get now.

    • djs25uk's avatar
      djs25uk
      Icon for Helper I rankHelper I
      6 years ago

      Thank you so much for your reply Ryan. 

      At first I thought you meant to add this as a measure but I tried that and it gives a really high result which isn't correct. I couldn't work out how you meant to add this as a filter. I am so sorry to ask but would you mind explaining where I add this?

       

      I do wonder whether it will be useful for me to try and fill my table with the missing data as £0 as I will need to count the number of missing payments quite frequently. Does anyone else have any experience with having done this?

       

      Thank you so much for your help.

      Dan

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        6 years ago

        djs25uk 

         

        Do you want to fill in the missing data or get the result?

         

        If you want to fill in the missing data, you need a table with full datetime,use lookupvalue to get the value.

        Column = LOOKUPVALUE('Table'[payment],'Table'[paymentdate],datetime[Date])+0

        If you want to get the correct result,you need to use the month and quarter column as filter.

        Measure = sum('Table'[payment])/DISTINCTCOUNT(datetime[month])

        Used distinct count to count number of month, then will be (17+17)/3. The average value will be less than what you get.

        Hope this is helpful.

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    Drag Year and Month from the Calendar Table and write this measure

    =SUM(Data[Paymentamount)+0

    Does this help?