CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktop
Serge Inácio
Serge Inácio

Reputation: 1382

PowerQuery - add column with end of month where the last day is not included in the data

I have a table with dates and would like to add a column to display the end of the month.

Normally this is simple, but my problem is, in some cases the last day of the month is not included in the data. I would like to add the last day available in the report as the last day of the month:

Date format: mm/dd/yy

Date End Of month What I need
01/10/2023 31/10/2023 20/10/2023
06/10/2023 31/10/2023 20/10/2023
10/10/2023 31/10/2023 20/10/2023
20/10/2023 31/10/2023 20/10/2023
05/09/2023 30/09/2023 29/09/2023
16/09/2023 30/09/2023 29/09/2023
29/09/2023 30/09/2023 29/09/2023

Is there a simple way to achieve that?

Thank you for any help.

Upvotes: 1

Views: 812

Answers (2)

Sam Nseir
Sam Nseir

Reputation: 12111

And in DAX if you wanted a Calculate Column:

EndOfMonth = 
  var cDate = [Date]
  RETURN CALCULATE(LASTDATE('YourTable'[Date]), ALL('YourTable'), 'YourTable'[Date] <= EOMONTH(cDate, 0))

Upvotes: 1

horseyride
horseyride

Reputation: 21373

In powerquery (date format irrelevant)

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastDate",  (x)=>List.Max(Table.SelectRows(#"Changed Type", each Date.EndOfMonth(x[Date])=Date.EndOfMonth([Date]))[Date]),type date)
in #"Added Custom"

enter image description here

alternate

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Date])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"End of Month"}, {{"Max Date", each List.Max([Date]), type nullable date}, {"data", each _, type table [Date=nullable date, End of Month=date]}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Date"}, {"Date"})
in  #"Expanded data"

Upvotes: 1

Related Questions