Calculated column - duration between previous payment

[Anonymous]

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.

[dax]

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.

[Anonymous]

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 ?