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
Anonymous
Not applicable

Creating a rolling calendar table with only the first day of each month

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.

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

AlexisOlson_0-1641505377930.png

 

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

 

View solution in original post

11 REPLIES 11
MightyMike
New Member

Hi There, 
Not so elegant, but a workaround:

To the already existing column, Just add another column Start of Month:

MightyMike_0-1676975924387.png

 

AlexisOlson
Super User
Super User

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

AlexisOlson_0-1641505377930.png

 

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

 

Shelley
Continued Contributor
Continued Contributor

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

@racs 

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.

Anonymous
Not applicable

Thank you very much!

parry2k
Super User
Super User

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

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

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.

Anonymous
Not applicable

Thank you!

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.