Hi,
I have the below step in Power Query:
#"Added Custom3" = Table.AddColumn(#"Changed Type4", "RF Collection Day", each if #date((Date.Year(DateTime.LocalNow())),(Date.Month(DateTime.LocalNow())),[RF.dd_collection_day]) <= Date.From(DateTime.LocalNow()) then #date((Date.Year(DateTime.LocalNow())),(Date.Month(DateTime.LocalNow())+1),[RF.dd_collection_day]) else #date((Date.Year(DateTime.LocalNow())),(Date.Month(DateTime.LocalNow())),[RF.dd_collection_day])),
This has been working fine, until today. The 31st of a month where the following month doesn't have 31 days. What the above is meant to do is:
if the year today, month today and RF.dd_collection_day is less than or equal to today, then add 1 month
But today, that creates the date 31st September 2023, which doesn't exist.
How do I update the step so that where the calculated date is greater than number of days in the relevant month, move the date to the 1st of the following month?
I have the below step in Power Query:
#"Added Custom3" = Table.AddColumn(#"Changed Type4", "RF Collection Day", each if #date((Date.Year(DateTime.LocalNow())),(Date.Month(DateTime.LocalNow())),[RF.dd_collection_day]) <= Date.From(DateTime.LocalNow()) then #date((Date.Year(DateTime.LocalNow())),(Date.Month(DateTime.LocalNow())+1),[RF.dd_collection_day]) else #date((Date.Year(DateTime.LocalNow())),(Date.Month(DateTime.LocalNow())),[RF.dd_collection_day])),
This has been working fine, until today. The 31st of a month where the following month doesn't have 31 days. What the above is meant to do is:
if the year today, month today and RF.dd_collection_day is less than or equal to today, then add 1 month
But today, that creates the date 31st September 2023, which doesn't exist.
How do I update the step so that where the calculated date is greater than number of days in the relevant month, move the date to the 1st of the following month?
