Get a list of dates from range and schedule

[mzrkwcz]

I am making a sales etimates report and I'm stuck at the problem of using the deal information to create the table of future invoices.

I have a table of Deals with following fields:

• period_start = DATE
• period_end = DATE
• schedule = one of ("once", "monthly", "quarterly")

Now I need to create the table of invoices, with the date column:

• if schedule is "once", it should be just one row containing period_start
• if schedule is "monthly", I want to get one row with period_start date, and following rows with dates incremented by 1 month but not exceeding period_end date
• if schedule is "quarterly", I want to get similar result like above, with 3 months increment.

I have no idea how to start... can you suggest something, please?

[sam241]

How about this? (for anyone else who's still interested)

= Table.AddColumn( Source, "invoice_date", each

if schedule = "once" then period_start else

if schedule = "monthly" then List.Generate( () => [ a = [period_start], b = [period_end] ], each [a] <= [b], each [ a = Date.AddMonths( [a], 1 ), b = [b] ], each [a] ) else

if schedule = "quarterly" then List.Generate( () => [ a = [period_start], b = [period_end] ], each [a] <= [b], each [ a = Date.AddQuarters( [a], 1 ), b = [b] ], each [a] ) else

null

)

And then expand the lists, as normal.

In case it helps you, this is the reference document for the List.Generate function.

[Greg_Deckler]

I'm not sure this is the best scenario for Power BI. It sounds like you are trying to "invent" data using Power BI and that's probably not what you should be using Power BI for. You might be able to get there with something like 3 CALCULATETABLE expressions and then a UNION or two but dicey. There also is likely an M code solution to this where you would call functions that would return the data you need for your invented data. But, you probably should use Excel to generate the data and then just import it into Power BI.

[mzrkwcz]

I was thinking of using M code (to create a column with the list of dates in Deals table)...

I do not think Excel is a solution here - I am using the data read directly from the REST API of SaaS CRM, Pipedrive. It is updated automatically and results distributed with shared dashboards are used by other team members

[Greg_Deckler]

In theory you could do this via M code with functions but that's a bit beyond me but @ImkeF could probably show you the way. If she can't, then it's not even possible.

[ImkeF · Accepted solution]

This should get you started:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then {[period_start]} else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"

It creates additional rows per month or quarter with their respective 1st days.

@Greg_Deckler: Do you know where to post/adress forum bugs like the missing signatures ?

[JohnThomas]

Awsome!  Love this code.  We use it to generate a good data set from a workbook that has start date / end date for when we Hire and ROF (Reduction of Force) Craft employees.  This, given the shift they are working (5/10's for example, would be 5 days per week, 10 hours per day) allows us to calculate forecast cost and hours with straight time and overtime by day!  One simple PowerQuery step, turns a super complicated workbook into a simple 1 line per employee.  Additionally, we can now generate daily, weekly, monthly, quarterly charts and tables by employee, discipline, work area, ets.

I love PQ!

[mzrkwcz]

Wow, thanks ImkeF, it looks like you made me started, thanks.

I am fresh new at M, so I need to experiment with this for a while...