Calculate how many standing orders per week and their value given set frequency

[Quoc_Tran]

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.

[PaulOlding]

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:

Number of standing orders =

VAR _EndDate = DATE(2022,3,31)

VAR _StandingOrderPayments =

GENERATE(

    'Standing Orders',

    FILTER('Date',

        'Date'[Date] >= 'Standing Orders'[Start Date]

        && 'Date'[Date] <= _EndDate

        && MOD(INT('Date'[Date] - 'Standing Orders'[Start Date]), 'Standing Orders'[Frequency]) = 0

        )

    )

VAR _Result = COUNTROWS(_StandingOrderPayments)

RETURN

_Result

Total Value =

VAR _EndDate = DATE(2022,3,31)

VAR _StandingOrderPayments =

GENERATE(

    'Standing Orders',

    FILTER('Date',

        'Date'[Date] >= 'Standing Orders'[Start Date]

        && 'Date'[Date] <= _EndDate

        && MOD(INT('Date'[Date] - 'Standing Orders'[Start Date]), 'Standing Orders'[Frequency]) = 0

        )

    )

VAR _Result = SUMX(_StandingOrderPayments, 'Standing Orders'[Value])

RETURN

_Result

[Quoc_Tran]

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?

[PaulOlding]

Power Query's not my speciality.  I expect they'll be others in the forum who can do that.