Last 15th of a month from Today

[mb0307]

Hi all,

Code below which gives me Today's date:

let
    Today = Date.From(DateTime.FixedLocalNow()),
    YearToday = Text.From(Date.Year(Today)),
    MonthToday = "0"&Text.From(Date.Month(Today)),
    DayToday = "0"&Text.From(Date.Day(Today)),
    CreateTextForQueryEnd = Text.End(DayToday,2) &"/"& Text.End(MonthToday,2) &"/"& YearToday
  
in
    CreateTextForQueryEnd

But I want to get the LAST 15th of a month. 

Example:

• on 14th DEC,  it should show 15th NOV
• on 16th DEC,  it should show 15th DEC
• on 14th JAN 2021,  it should show 15th DEC 2020

Thanks for your help.

[Fowmy]

@mb0307 

Yes, it should, try the following code, you can change the YY, MM,DD to test

let

    Today = #date(2020,01,31),  //Date.From(DateTime.FixedLocalNow()),
    PreMonth15 = Date.AddDays(Date.StartOfMonth( Date.AddMonths(Today,-1)),14)
in
    PreMonth15

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[mb0307]

@Fowmy not giving the correct result.  Please see example below. 

on 16th Dec,  it should show LAST 15th i.e. 15th Dec

Similarly

Example:

• on 14th DEC,  it should show 15th NOV
• on 16th DEC,  it should show 15th DEC
• on 14th JAN 2021,  it should show 15th DEC 2020

[Fowmy · Accepted solution]

@mb0307 

i missed that point: Try this oneplease.

let

    Today = #date(2020, 1, 14),//Date.From(DateTime.FixedLocalNow()),
    PreMonth15 = Date.AddDays(Date.StartOfMonth( Date.AddMonths(Today,if Date.Day(Today) > 15 then 0 else -1)),14)
in
    PreMonth15

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[mb0307]

@Fowmy  exactly what I wanted.  Thanks a lot for such a quick response.

[Fowmy]

@mb0307 

Check this code:

let

    Today = Date.From(DateTime.FixedLocalNow()),
    PreMonth15 = Date.AddDays(Date.StartOfMonth( Date.AddMonths(Today,-1)),14)
in
    PreMonth15

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[mb0307]

@Fowmy  Thanks.

it worked and given me 15th Nov.  But will it work once current date is >= 15th Dec (for that it should give me 15th Dc)? 

Thanks