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.
Hi,
I would like some help to calculate future trends of standing orders given each one has a set frequency, different start dates and amount. For example the table below
Contract | Start date | Frequency (days) | Value |
123ABC | 01/06/2021 | 28 | £100 |
456DEF | 22/06/2021 | 7 | £50 |
So each week will provide information on how many standing order contracts fall within that week and their total values. I.e.
Week commencing | Number of standing orders | Total value |
31/05/2021 | 1 | £100 |
07/06/2021 | 0 | 0 |
14/06/2021 | 0 | 0 |
21/06/2021 | 1 | £50 |
28/06/2021 | 2 | £150 |
05/07/2021 | 1 | £50 |
And so on until a set date, say 31/03/2022.
Depending on what your data volumes are and performance requirements you should consider creating a 'Standing Order Payments' table that will list out the individual dates based on Start Date and Frequency. You could use Power Query to do this. The table would look like:
Contract | Value | Standing Order Date |
123ABC | 100 | 01/06/2021 |
456DEF | 50 | 22/06/2021 |
123ABC | 100 | 29/06/2021 |
456DEF | 50 | 29/06/2021 |
456DEF | 50 | 06/07/2021 |
456DEF | 50 | 13/07/2021 |
You can then join a date table that includes 'Week Starting Date' column to Standing Order Date and you're ready to count and sum your standing orders.
It's possible to get your example result without doing that. These measures includes a GENERATE function call which essentially creates the table above on the fly.
These measures also use a date table, but disconnected from the Standing Orders table:
Yes I think a Standing Orders Payment table may be a good idea. Do you know how I would use Power Query to create such a table using the Start dates and Frequency already in the Contracts table?
Power Query's not my speciality. I expect they'll be others in the forum who can do that.
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 |
---|---|
47 | |
25 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |