Forum Discussion
Calculated column - duration between previous payment
Most of the time when I ask something is about a specific technical question, here I have a demand and I know even know how to start. Trying to start this new dashboard it's the first time that I'm stuck how to approach this. So in despair I would ask some help.
I have a classical payment table where we store id, organization_id (<- who receive the money), date, amount... you named it !
I have to build a dashboard where there is a matrix.
The v1 of this dashboard is to answer that : "I want to know the number of organizations where theirs last payment was the last month (so 2018-12 for the row 2019-01) | number where the last payment was M-2, M-3... to have a view of when the oarganizations where last active"
The prototype looks like this :
| M-1 | M-2 | M-3 | M-4 | M-5 | M-6 | M-[...] | |
| Jan-19 | B3 | G3 | |||||
| Feb-19 | B4 | C4=B3-B4 | |||||
| Mar-19 | |||||||
| Apr-19 | |||||||
| May-19 | |||||||
| Jun-19 | |||||||
| Jul-19 | |||||||
| Aug-19 | |||||||
| Sep-19 | |||||||
| oct.-19 | |||||||
| nov.-19 | |||||||
| Dec-19 | B14 | G14 | |||||
| B3 = # organizations where last payment was in 2018-12 the 2019-01-01 | |||||||
| G3 = # organizations where last payment was in 2018-07 the 2019-01-01 | |||||||
| B4 = # organizations where last payment was in 2019-01 the 2019-02-01 | |||||||
| B14 = # organizations where last payment was in 2019-11 the 2019-12-01 | |||||||
| G14 = # organizations where last payment was in 2019-06 the 2019-12-01 |
I think that I have to make a new calculated column where for each rows in my payment table I have to calculate the number of months with the previous payment for the distinct organization. And don't know if it's the best approach and how to build this calculated column. My tries fail for now.
2 Replies
Hi gilHA,
I am not clear about your requirement, what did you mean of “I want to know the number of organizations where theirs last payment was the last month (so 2018-12 for the row 2019-01) | number where the last payment was M-2, M-3... to have a view of when the oarganizations where last active”? If possible, could you please inform me in details(your sample data and your expecting output)?
In addition, if your dataset sample is like below, and you want to get result like below, you could try to use below expression
previous = VAR previousm = DATEADD ( 'time'[ytime], -1, MONTH ) RETURN CALCULATE ( SUM ( 'time'[amount] ), FILTER ( 'time', 'time'[ytime] = previousm ) ) - 'time'[amount]pre 6 month = VAR p6m = DATEADD ( 'time'[ytime], -6, MONTH ) RETURN CALCULATE ( SUM ( 'time'[amount] ), FILTER ( 'time', 'time'[ytime] = p6m ) ) - 'time'[amount]Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi dax,
Thank you for your help. I will try to be a bit more precise then.
I follow the video I have mention earlier and calculated the previous payment date for each paiment ordered by organization and date. Then I calculated the number of month between the current and the later payment cheching first if the organization_id is the same, if not I add -1 as a value. It's not really what I'm looking for because I wanna check not exactly the last payement but id there was a payment the last month, or two month before... but it's a start.
If we focus on the data of one unique organization that should look like this :
id organization_id amount date prev_organization_id prev_id_payment prev_date delta_date 2830649 1234 5 2018-09-29 22:17:00 7890 2830637 2018-09-29 22:17:00 -1 2830915 1234 10 2018-09-30 00:44:00 1234 2830649 2018-09-29 22:17:00 0 2832391 1234 40 2018-11-15 13:41:00 1234 2830915 2018-09-30 00:44:00 2 2832506 1234 5 2018-12-07 14:07:00 1234 2832391 2018-11-15 13:41:00 1 2832588 1234 10 2018-12-11 14:28:00 1234 2832506 2018-12-07 14:07:00 1 2832621 1234 10 2019-01-07 14:34:00 1234 2832588 2018-12-11 14:28:00 1 2832656 1234 5 2019-01-08 15:39:00 1234 2832621 2019-01-07 14:34:00 1 2832678 1234 10 2019-01-09 17:44:00 1234 2832656 2019-01-08 15:39:00 1 2832751 1234 100 2019-01-17 14:26:00 1234 2832678 2019-01-09 17:44:00 1 2832773 1234 37 2019-03-21 14:56:00 1234 2832751 2019-01-17 14:26:00 2
So this organization was "active" (at least one payment) in september, nov, dec, jan amd mar.So the final table (if I have to filter on this particular organization, but obviously I want it on my full data) will look like this :
Date M+1 M+2 M+3 01/09/2018 0 ← First payment receive 01/10/2018 1 ← Had a payment last month 01/11/2018 0 1 ← Last payment from 2 months ago 01/12/2018 1 01/01/2019 1 01/02/2019 1 01/03/2019 0 1 ← Last payment from 2 months ago Is that a better explanation ?
