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'm trying to generate a list of jobs to do in the next 2 years
I have a working custom column that provides me the list of dates.
But my jobs have varying frequencies of 2-12 months intervals.
List.Generate(() => [Next job],
each Date.From(_) <= Date.From(Date.AddYears(Date.EndOfYear(DateTime.LocalNow()), 1)),
each Date.AddMonths(_, 2))
Once I change my code to :
List.Generate(() => [Next job],
each Date.From(_) <= Date.From(Date.AddYears(Date.EndOfYear(DateTime.LocalNow()), 1)),
each Date.AddMonths( _ , [FREQUENCY]))
I get an error and when expanding further, it just shows me a date without any error message.
Anyone knows what is causing this issue, and how I can work around it ?
Thanks
Solved! Go to Solution.
It might be a field access error.
Try
let freq = [FREQUENCY] in each Date.AddMonths( _ , freq))
It might be a field access error.
Try
let freq = [FREQUENCY] in each Date.AddMonths( _ , freq))
It generates without issue once i added in this line
Thanks!
Hi ben-t,
Could you share the full code that worked for you?
I think I've tried every possible code with the above solution, but I still get an error.
Thanks!
Hi @GekkeHellie
this is the code i used
let freq = [FREQUENCY] in
List.Generate(
() => [Next job],
each Date.From(_) <= Date.From(Date.AddYears(Date.EndOfYear(DateTime.LocalNow()),1)),
each Date.AddMonths(_, freq))
Hi @ben-t ,
Your answer definitely helped me into the right direction.
In the table there is a start date, end date and frequency and I also wanted to create a new row between the two dates based on de frequency.
My table looks like this:
But since I have a end date in a column, instead of a formula like yours, I still got a error.
So I also added the below line to the code:
let enddate = [End Date]
let freq = [Frequency] in
let enddate = [End Date] in
List.Generate(
() => [Start Date],
each Date.From(_) <= Date.From(enddate),
each Date.AddDays(_, freq))
Thanks for helping me out!
Hi @ben-t
Its an invironment/ item access issue, here's a work around. Just assign the field value to a variable and bring that into your calculation.
let n = [FREQUENCY] in List.Generate(() => [Next job],
each Date.From(_) <= Date.From(Date.AddYears(Date.EndOfYear(DateTime.LocalNow()), 1)),
each Date.AddMonths(_, n ))
Ps. If this helps solve your query please mark this post as Solution, thanks!
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.