Including Missed Payments in Data

[djs25uk]

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:

PaymentID	PaymentStudentID	PaymentAmount	PaymentDate
1000	123456	17.00	01 Jan 2020
1002	123456	17.00	01 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

[Ashish_Mathur]

Hi,

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

=SUM(Data[Paymentamount)+0

Does this help?

[ryan_mayu]

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

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 · Accepted solution]

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

[djs25uk]

Truthfully, I am not sure which will be best in my data context because I'm still learning, but I will experiment with both and see what happens. 

I think I have discovered my problem is much more complex that I described above because of some start/end values in a related table which also need to be considered. I'll see what I can learn today with your help from above and will start a new post if I realise the problem is much more complicated. 

Thank you so much for your time both of you.