Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mzrkwcz
Helper I
Helper I

Get a list of dates from range and schedule

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?

1 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 ?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
sam241
Regular Visitor

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
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 ?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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!

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...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.