Custom Column: End of Month data and current date data

[crln-blue]

Hello!

I have a report where I need to show the current date's last 12 months data. I'm new to PowerBI and I looked for related situations. I found a very helpful article which solved my problem: https://medium.com/dm-p/generating-rows-by-month-for-date-ranges-in-power-query-9baf62ed8e99

However, the requirement was a bit different from the above link. I need to get the last day of each of the last 12 months from now and I also need to include the current date. So my expected result is:

11/19/2020 <- current date

10/31/2020

09/30/2020

08/31/2020

07/31/2020

06/30/2020

05/31/2020

04/30/2020

03/31/2020

02/29/2020

01/31/2020

12/31/2019

11/30/2019

Below is my current date formula:

Above formula provides month end date but I also need to insert the current date. How can I do so?

Any help is appreciated. Thank you!

[PhilipTreacy · Accepted solution]

Hi @crln-blue 

You can add today's date to the front of that Month List like this

List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]})

This will give you a list as a result. 

The full example query is

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcnJCQAgDATAXnwLZuNdS0j/bUhAYX0OY5ZQIEVFJXkObcZiTMZgdEZjVIYy8CEK+wlXfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Month])),
    Months = List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]})
in
    Months

You can add a column to the table like so 

= Table.FromColumns({{Table.ToColumns(#"Added Custom")},Months})

But you are inserting a new row, which is today's date, so the first row in your table will end up with nulls or a list.  If you could supply your data it might be easier to explain and implement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcnJCQAgDATAXnwLZuNdS0j/bUhAYX0OY5ZQIEVFJXkObcZiTMZgdEZjVIYy8CEK+wlXfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Month])),
    Months = List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]}),
    NewCol = Table.FromColumns({{Table.ToColumns(#"Added Custom")},Months})
in
    NewCol

But here's the full code and a sample PBIX

Regards

Phil

[v-yingjl · Accepted solution]

Hi @crln-blue ,

Followed by your previous query, the table is like this:

Combine current date and the Custom date to list and transform to table, the whole query is like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRSitUBUoYQygBMWYJJCzBpDibNwKQpmDQBk8ZgEqpbKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month List" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month List", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.EndOfMonth(
    Date.AddMonths(
        Date.From(DateTime.LocalNow()),
        0 - 13 + [Month List]
    )
)),
    Custom1 = List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[Custom]}),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy · Accepted solution]

Hi @crln-blue 

You can add today's date to the front of that Month List like this

List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]})

This will give you a list as a result. 

The full example query is

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcnJCQAgDATAXnwLZuNdS0j/bUhAYX0OY5ZQIEVFJXkObcZiTMZgdEZjVIYy8CEK+wlXfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Month])),
    Months = List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]})
in
    Months

You can add a column to the table like so 

= Table.FromColumns({{Table.ToColumns(#"Added Custom")},Months})

But you are inserting a new row, which is today's date, so the first row in your table will end up with nulls or a list.  If you could supply your data it might be easier to explain and implement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcnJCQAgDATAXnwLZuNdS0j/bUhAYX0OY5ZQIEVFJXkObcZiTMZgdEZjVIYy8CEK+wlXfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Month])),
    Months = List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]}),
    NewCol = Table.FromColumns({{Table.ToColumns(#"Added Custom")},Months})
in
    NewCol

But here's the full code and a sample PBIX

Regards

Phil