Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Is it possible to create a rolling calendar which shows only the first day of each month? I've currently generated a rolling callendar with all the dates until today, by accessing "Get data" -> "Blank query", using the following formula:
=#date(2021, 1, 1)
and then adding the custom step:
= List.Dates (Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration(1,0,0,0))
Is there a way to change the custom step in order to only get the first day of each month? Thanks in advance.
Solved! Go to Solution.
You can transform that list you've got like this:
let
Source = #date(2021, 1, 1),
ListDays = List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration(1,0,0,0)),
ListMonths = List.Distinct(List.Transform(ListDays, each Date.StartOfMonth(_)))
in
ListMonths
A different approach would be to calculate the number of months you need and use Date.AddMonths. The downside is that there isn't a nice clean way to calculate the months (as far as I can find).
Not pretty but works:
let
Source = #date(2021, 1, 1),
Months = Number.Round(Number.From((DateTime.LocalNow() - DateTime.From(Source))/( 365.25 / 12 )), 0),
ListMonths = List.Transform({0..Months}, each Date.AddMonths(Source, _))
in
ListMonths
Hi There,
Not so elegant, but a workaround:
To the already existing column, Just add another column Start of Month:
You can transform that list you've got like this:
let
Source = #date(2021, 1, 1),
ListDays = List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration(1,0,0,0)),
ListMonths = List.Distinct(List.Transform(ListDays, each Date.StartOfMonth(_)))
in
ListMonths
A different approach would be to calculate the number of months you need and use Date.AddMonths. The downside is that there isn't a nice clean way to calculate the months (as far as I can find).
Not pretty but works:
let
Source = #date(2021, 1, 1),
Months = Number.Round(Number.From((DateTime.LocalNow() - DateTime.From(Source))/( 365.25 / 12 )), 0),
ListMonths = List.Transform({0..Months}, each Date.AddMonths(Source, _))
in
ListMonths
Thanks for this, but I must be doing something wrong. I'm getting this error:
DataFormat.Error: We couldn't convert to Number.
Details:
[Table]
Here's the script:
let
Source = #"Repair Orders",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Created Date"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Created Date", Order.Ascending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"Changed Type" = Table.TransformColumnTypes(#"Kept First Rows",{{"Created Date", Int64.Type}}),
#"Filled Down" = List.Dates(#"Changed Type", Number.From(DateTime.LocalNow()) - Number.From(#"Changed Type"), #duration(1,0,0,0)),
#"Filled Down2" = List.Distinct(List.Transform(#"Filled Down", each Date.StartOfMonth(_)))
in
#"Filled Down2"
I'm trying to take the oldest repair order date (sorted in ascending order and then kept first row) and create a calendar with the first of the month for every month to today. I'm getting a number conversion error and even tried doing the formatting in the previous step. Any chance you can quickly notice what I'm doing wrong?
Hi,
Is there a way that we can restrict the current date to previous month. I want to use it for YTD calculations, that is uptill last closed month!
thank you
You could use List.RemoveLastN to remove the last month or use List.Select to filter your list with whatever condition you want.
Hi Alexis,
Great solution. Can we also tweak this formula to add End of month days as well. Such that the output will be like:
01/01/2022
31/01/2022
01/02/2022
28/02/2022
01/03/2022
31/03/2022
and so on...
@sayang194 Yeah, do the same thing for the end of month and then combine the lists.
I haven't double-checked my syntax, but I'd expect it to look something like this:
let
Source = #date(2021, 1, 1),
ListDays = List.Dates(Source, Number.From(DateTime.LocalNow()) - Number.From(Source), #duration(1,0,0,0)),
ListMonthStart = List.Distinct(List.Transform(ListDays, each Date.StartOfMonth(_))),
ListMonthEnd = List.Distinct(List.Transform(ListDays, each Date.EndOfMonth(_))),
ListMonths = List.Sort(List.Combine({ListMonthStart, ListMonthEnd}))
in
ListMonths
This works perfectly. Thanks.
Thank you very much!
@Anonymous thru add columns, add a column based on the date column for the start of the month, remove date column, and then get distinct value for month column. It will get you the first date of each month.
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |