Generate recurring expenses

Hi all,

I have a tough one at hand…
The Bills export I get from our system does not generate the recurring ones, it just provides a info column with the Frequency type.
When an expense is recurring, i.e. every other month from the Transaction Date (lets suppose Oct 3rd 2021), I need to generate (at least for the current year) the remaining transactions - in this example one more on Dec 3rd 2021. I wouldn’t mind though if I could generate the next n transactions.
I need to do that for all different Frequency types, apart from ‘Only once’.

Can this be managed in Power Query? I am open to all suggestions :sunglasses:

SampleFinance.pbix (229.0 KB)

1 Like

Hi @Thimios,

I’m away now, but will have a look at your sample when I get back. In the meantime you can examine this topic, that seems similar.

I hope this is helpful

1 Like

WOW…
Never thought it would be that complicated :fearful:
Since I’m M illiterate, I will be definitely waiting for your return.

Hi @Thimios,

:thinking: I can recommend a PQ course if you want to get up to speed, you’ll find it in the portal…

I did some prepping. First created this supporting query (Important! know that PQ is case sensitive so these Freq names need to match with the Freq names in your ScheduledBIlls table).

image

Next you can optionally set an upper limit for creating new dates, should you omit that the last date in your date table will be the upper limit. However if you do set a date you can’t exceed beyond the last date in your date table.

image

and finally I created this custom function: fxCreateDates

( StartDate as date, myFrequency as text, optional endDate as any ) as list =>
    let
        FreqList = List.Buffer(tInterval[Frequency]),
        IntervalList = List.Buffer(tInterval[Interval]),
        Interval = IntervalList{List.PositionOf(FreqList, myFrequency)}?,
        maxDate = if endDate <> Date.Type or endDate = null then Date.From(CalenderMax) else List.Min( { Date.From(CalenderMax), Date.From(endDate) }),
        Source = List.Generate(
                () => [x = 0, y = maxDate, z = StartDate], 
                each [z] <= [y] and not List.IsEmpty( List.RemoveNulls( {Interval} )), 
                each [x = [x] + 1, y = [y], z = Date.AddMonths( [z], Interval )]
            ),
        CreateTable = Table.FromRecords(Source)
    in
        try CreateTable[z] otherwise {StartDate}

.
Invoked that on your ScheduledBIlls table and expanded the lists to new rows.

Here’s your sample file:
eDNA - Payment periods create dates.pbix (233.5 KB)

I hope this is helpful

2 Likes

:clap: :clap: :clap: @Melissa
Your custom function works like a charm, I couldn’t make it without your help.
Thank you.
PS. Tried to understand the function but my intermediate BI level is not enough :cold_face:

1 Like