check if a financial month is closed

[Evandam]

I have created a dynamic date table in Power Query.

I now want to add a column that shows if a the financial month is closed.

Monthend close is ready each 15th of the month.

So the month may 2023 is closed on the 15th of june.

i have a date column as well as a year-month column available in the calender table i created in Power Query.

Difficulty i think occurs in february and because i want to take into account the 15th of the current month.

Who can assist/help me with this challenge?

[lbendlin]

Where do you plan to store that "closed" flag?  In your calendar table?

[Evandam]

@lbendlin yes in the calender table

[lbendlin]

You can flag any date that is older than TODAY()-15.

[Evandam]

imo not the best approach because some months with 31 days, 30 and even 28 or 29 days. 

I think this will hold up:

each if

Date.Year([Date])<

Date.Year(Date.From(DateTime.LocalNow()))

then "TRUE"

else if Date.Year([Date]) >= Date.Year(Date.From(DateTime.LocalNow())) and  Date.Month([Date]) < Date.Month(Date.From(DateTime.LocalNow())) and Date.Day(Date.From(DateTime.LocalNow())) >14

then "TRUE" else "FALSE"

Can you agree or ??

[Evandam · Accepted solution]

The proper solution is the following:

if

Date.Year([Date]) < Date.Year(Date.From(DateTime.LocalNow()))

then

1

else if

Date.Day(Date.From(DateTime.LocalNow())) <15 

and Date.Month([Date]) < Date.Month(Date.From(DateTime.LocalNow())) -2

then 1

else if

Date.Month([Date]) < Date.Month(Date.From(DateTime.LocalNow())) -1

then 1

else 0