cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mb0307
Post Patron
Post Patron

Last 15th of a month from Today

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.

1 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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy not giving the correct result.  Please see example below. Screenshot 2020-12-08 173629.jpg

 

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

 

 

 

 

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

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

Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@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 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors