End-of-Month, End-of-Week, Week Number, End-of-Quarter | Boolean from Booking Date

[maier_alexander_MO]

Dear all,

I have currently built a first workflow which aggregates various data sources into one large table that basically shows different accounts for each day with their respective amounts for that booking day.

In the next step I want to create a dashboard in Tableau, but for that would urgently require the information whether the date of this line item is the End-of-Month, End-of-Week and which Calendar Week and whether the date is the End-of-Quarter.

This is required since the data can only be reported for a single day since summing account balances makes no sense (compared to like for example cashflows). Thus in the end, I could simply add the column EOW in Tableau as a filter and easily show the relevant amounts.

The down below shows how the 4 additional rows should look like:

Booking Date	EOM	EOW	CW	EOQ
31/08/2019	True	False	35	False
13/09/2019	False	False	37	False
15/09/2019	False	True	37	False
30/09/2019	True	False	40	True

Do any of you have had the same challenge and already found formulas for this? The formulas should always check for the actual last day of a month/week/quarter (not the last working day) since my formulas update weekend days with the amounts from Friday anyways.

Thank you very much in advance for your help on this!

Kind regards,

Alex

[jdunkerley79 · Accepted solution]

 I would suggest:

EoM: %b gives the month

DateTimeFormat([Date],"%b") != DateTimeFormat( DateTimeAdd([Date],1,"days"),"%b")

EoW (based on the sample you gave Monday is the first day of the week): %a - gives the day of the week

DateTimeFormat(DateTimeAdd([Date],1,"days"),"%a")="Mon"

Cw:

DateTimeFormat([Date],"%W")

EoQ is a little fiddly - I suggest:

DateTimeFormat([Date],"%b") != DateTimeFormat( DateTimeAdd([Date],1,"days"),"%b")
 && Mod(DateTimeMonth([Date]),3)=0

Sample attached

[danilang]

Hi @maier_alexander_MO 

Just a small addition to @jdunkerley79's post

Any time you need to find the end of the month you can use DateTimeTrim([Field], "lastofmonth").  So The EoM calculation becomes

[Date] = ToDate(DateTimeTrim([Date],"lastofmonth"))

Dan 

[maier_alexander_MO]

Hi @jdunkerley79 thank you so much for your quick and detailed answers! I implemented the formulas and it is working perfectly and as desired 🙂

@danilang thank you very much for the addition on how to get the end of the respective month - that will actually help a lot for my workflow as well!

I am really thankful for your help on this, have a great day!

Kind regards,

Alex