Redate dates earlier than this month as this month

[BBBB]

Can anyone help with the M language for redating any previous dates as the current month? It can be redated as any date in the month as I then change it to Month and year.

I'm sure It's probably simple using:

If Date.IsInPreviousMonth then....

But I cant figure it out and cant find any related posts on any forums.  

[Chewdata · Accepted solution]

@BBBB plesae try the following:

1. Create a new blank query. Open the advanced editor and replace the contents with:

(vDate as date) => 
    let
        // Get the current date
        CurrentDate = DateTime.LocalNow(),

        // Extract the current year and month
        CurrentYear = Date.Year(CurrentDate),
        CurrentMonth = Date.Month(CurrentDate),

        
        AddCustom = if Date.IsInPreviousNMonths(vDate, 100) then Date.FromText(Text.From(CurrentYear) & "-" & Text.PadStart(Text.From(CurrentMonth), 2, "0") & "-01")
        else vDate
        
    in
        AddCustom

2. In your main query open the advanced editor. Put a  comma , behind the last line before the "in" part and add the following step:

inv_fnRedate = Table.TransformColumns(Source,{{"Date", fnRedate, Int64.Type}})

3. replace the 'in' part with:

in
    inv_fnRedate

[p45cal · Accepted solution]

A bit more efficient than my previous suggestion; either this to transform your current dates column to new dates (no added column):

let
Source = Table.FromRecords({
[Date = #date(2024, 9, 15)],
[Date = #date(2024, 8, 10)],
[Date = #date(2024, 7, 5)],
[Date = #date(2024, 5, 5)],
[Date = #date(2020, 7, 5)]
}),
    NewDate = Date.StartOfMonth(Date.From(DateTime.LocalNow())),
    Result = Table.TransformColumns(Source,{{"Date", each if _ < NewDate then NewDate else _,type date}})
in
    Result

or this to add a new column:

let
Source = Table.FromRecords({
[Date = #date(2024, 9, 15)],
[Date = #date(2024, 8, 10)],
[Date = #date(2024, 7, 5)],
[Date = #date(2024, 5, 5)],
[Date = #date(2020, 7, 5)]
}),
    NewDate = Date.StartOfMonth(Date.From(DateTime.LocalNow())),
    Result = Table.AddColumn(Source, "New Date", each if [Date]<NewDate then NewDate else [Date], type date)
in
    Result

[p45cal]

Try (to include converting dates more than a month ago):

= Table.AddColumn(PreviousStep, "New Date", each if [Date] < Date.StartOfMonth(Date.From(DateTime.LocalNow())) then #date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1) else [Date])

[BBBB · Accepted solution]

Resolved:

= Table.AddColumn(#"Added Custom", "New Date", each if Date.IsInPreviousNMonths([DespatchDate], 100) then #date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1) else [DespatchDate])

This gives me the new dates as 01st of the current month.

I dont mind then adding another step to change it to month and year.

So satisfying when you finally get it to work. Thank you all for your help 

[p45cal · Accepted solution]

A bit more efficient than my previous suggestion; either this to transform your current dates column to new dates (no added column):

let
Source = Table.FromRecords({
[Date = #date(2024, 9, 15)],
[Date = #date(2024, 8, 10)],
[Date = #date(2024, 7, 5)],
[Date = #date(2024, 5, 5)],
[Date = #date(2020, 7, 5)]
}),
    NewDate = Date.StartOfMonth(Date.From(DateTime.LocalNow())),
    Result = Table.TransformColumns(Source,{{"Date", each if _ < NewDate then NewDate else _,type date}})
in
    Result

or this to add a new column:

let
Source = Table.FromRecords({
[Date = #date(2024, 9, 15)],
[Date = #date(2024, 8, 10)],
[Date = #date(2024, 7, 5)],
[Date = #date(2024, 5, 5)],
[Date = #date(2020, 7, 5)]
}),
    NewDate = Date.StartOfMonth(Date.From(DateTime.LocalNow())),
    Result = Table.AddColumn(Source, "New Date", each if [Date]<NewDate then NewDate else [Date], type date)
in
    Result

[BBBB]

Thank you for your suggestion. I'm currently unable to make it work but I'm persevering.

Can you explain the 'PreviousStep' bit please?

I've tried simplifying the formula to:

= Table.AddColumn(#"Added Custom", "New Date", each if [DespatchDate] < Date.StartOfMonth(Date.From(DateTime.LocalNow())) then (DateTime.LocalNow()) else [DespatchDate])

And then

= Table.AddColumn(#"Added Custom", "New Date", each if [DespatchDate] (Date.IsInPreviousMonth) then (DateTime.LocalNow()) else [DespatchDate])

But I keep getting error returned.

[bhanu_gautam]

@BBBB , You can try below m-code

m
let
// Replace this with your actual data source
Source = Table.FromRecords({
[Date = #date(2023, 9, 15)],
[Date = #date(2023, 8, 10)],
[Date = #date(2023, 7, 5)]
}),

// Get the current date
CurrentDate = DateTime.LocalNow(),

// Extract the current year and month
CurrentYear = Date.Year(CurrentDate),
CurrentMonth = Date.Month(CurrentDate),

// Add a custom column to redate previous dates to the current month
AddCustom = Table.AddColumn(Source, "RedatedDate", each
if Date.IsInPreviousMonth([Date]) then
Date.FromText(Text.From(CurrentYear) & "-" & Text.PadStart(Text.From(CurrentMonth), 2, "0") & "-01")
else
[Date]
)
in
AddCustom

[Chewdata]

Hey,

I added a little to your solution by making it a function that can be called in a Table.TransformColumn. This way you don't have to add a new column and remove the old.

let
// Replace this with your actual data source
Source = Table.FromRecords({
[Date = #date(2024, 9, 15)],
[Date = #date(2024, 8, 10)],
[Date = #date(2024, 7, 5)],
[Date = #date(2024, 5, 5)],
[Date = #date(2020, 7, 5)]
}),
    fnRedate = (vDate as date) => 
    let
        // Get the current date
        CurrentDate = DateTime.LocalNow(),

        // Extract the current year and month
        CurrentYear = Date.Year(CurrentDate),
        CurrentMonth = Date.Month(CurrentDate),

        
        AddCustom = if Date.IsInPreviousNMonths(vDate, 100) then Date.FromText(Text.From(CurrentYear) & "-" & Text.PadStart(Text.From(CurrentMonth), 2, "0") & "-01")
        else vDate
        
    in
        AddCustom,

    inv_fnRedate = Table.TransformColumns(Source,{{"Date", fnRedate, Int64.Type}})
in
    inv_fnRedate

[Chewdata · Accepted solution]

@BBBB plesae try the following:

1. Create a new blank query. Open the advanced editor and replace the contents with:

(vDate as date) => 
    let
        // Get the current date
        CurrentDate = DateTime.LocalNow(),

        // Extract the current year and month
        CurrentYear = Date.Year(CurrentDate),
        CurrentMonth = Date.Month(CurrentDate),

        
        AddCustom = if Date.IsInPreviousNMonths(vDate, 100) then Date.FromText(Text.From(CurrentYear) & "-" & Text.PadStart(Text.From(CurrentMonth), 2, "0") & "-01")
        else vDate
        
    in
        AddCustom

2. In your main query open the advanced editor. Put a  comma , behind the last line before the "in" part and add the following step:

inv_fnRedate = Table.TransformColumns(Source,{{"Date", fnRedate, Int64.Type}})

3. replace the 'in' part with:

in
    inv_fnRedate

[BBBB]

[BBBB]

Thanks for your suggestion. I'm currently unable to make it work but I'm persevering.