Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |