EndOffMonth / Previous Month - Power Query

Power Query
1

Good morning,

I need help to write a conditional Statement in Power query.

Basically, I am looking to check if today is the end of the month, and if it is not, I need the date of the previous month.

At the moment, I do not know how to bring the end of the previous month and my If Statement is not working either!

let

Endmonth = Date.EndOfMonth(DateTime.LocalNow()),
Today =  DateTime.LocalNow(), 
EndPreviousMonth = "Need Function", 
MonthEnd = if #"Today" = #"EndMonth" then #"Today" else "EndPreviousMonth"

in
MonthEnd

Attached is pbix with just this function…
EndOfMonth - Query.pbix (13.0 KB)

This function will be used in a larger script where I need to pass the end of the month date!

Many thanks,
Ezequiel

2

@ezenunez ,

Try dropping this into a blank query:

let
Today = Date.From( DateTime.LocalNow() ),
LastDayMo = Date.EndOfMonth( Today ),
PrevMo = Date.AddMonths( Today, -1),
LastDayPrevMo = Date.EndOfMonth( PrevMo ),
MonthEnd = if Today = LastDayMo then Today else LastDayPrevMo

in
MonthEnd

  • Brian
3

That is super great Brian! You make it look so simple!

My next challenge is to add this information in a String that will be used to bring info from an API.

How do you add this to a String? Is this possible or do I need to use a parameter?

Foro_PQ_String
Foro_PQ_String1193Ă—446 33.4 KB

By the way, I looking forward to the next Power query course!

Thanks Brian.

4

Hi @ezenunez

Try this

MonthEndDate=" & Text.From(#“MonthEnd”) & “NumberOfMonthIncluded=6”

5

I had a few goes but not yet there!
Any other sugestion?

// Find the last end of the month available

Today = Date.From( DateTime.LocalNow() ),
LastDayMo = Date.EndOfMonth( Today ),
PrevMo = Date.AddMonths( Today, -1),
LastDayPrevMo = Date.EndOfMonth( PrevMo ),
MonthEnd = if Today = LastDayMo then Today else LastDayPrevMo,

//Original
data = Json.Document(Web.Contents(ParamApiEndpoint & “/GetGeneralLedgerTrialBalanceReportData?MonthEndDate=2021-10-31&NumberOfMonthIncluded=36”,

// Test 1 data = Json.Document(Web.Contents(ParamApiEndpoint & “/GetGeneralLedgerTrialBalanceReportData?MonthEndDate=&Text.From(MonthEnd)&NumberOfMonthIncluded=36”,
// Test 2 data = Json.Document(Web.Contents(ParamApiEndpoint & “/GetGeneralLedgerTrialBalanceReportData?MonthEndDate=&Text.From(#“MonthEnd”)&NumberOfMonthIncluded=36”,
// Test 3 data = Json.Document(Web.Contents(ParamApiEndpoint & “/GetGeneralLedgerTrialBalanceReportData?MonthEndDate=&“Text.From(#“MonthEnd”)” &NumberOfMonthIncluded=36”,

6

Hi @ezenunez,

In your original code snippet your date is formatted as yyyy-mm-dd
This #“MonthEnd” query will return a string in that format

let
    Today = Date.From( DateTime.FixedLocalNow() ),
    MonthEnd = if Today = Date.EndOfMonth( Today ) then Today else Date.EndOfMonth( Date.AddMonths( Today, -1)),
    DateString = Text.Combine( List.Reverse( Text.SplitAny( Text.From( MonthEnd ), "-/" )), "-")
in
    DateString

Incorporating that code, will look something like:

data = Json.Document(Web.Contents(ParamApiEndpoint & “/GetGeneralLedgerTrialBalanceReportData?MonthEndDate="&#“MonthEnd”&"NumberOfMonthIncluded=36”,

I hope this is helpful

1 Like
7

Hi @ezenunez

you’ve to pass date in this format only

image

Change the date format

FinalDate = Text.From(Date.Year(MonthEnd))&"-"&Text.From(Date.Month(MonthEnd))&"-"&Text.From(Date.Day(MonthEnd))

Replace original code

MonthEndDate=" &FinalDate& “&NumberOfMonthIncluded=36”

9

Bumping this post for more visibility.

10

End solution:

Code from Melisa where return an String

Added to the string by inserting the result &" xxx"&

image
image1064Ă—184 11 KB

Thank you!

11

good job @Melissa …should have got the solution marked for it :slight_smile: