3 Replies Latest reply on May 1, 2018 5:54 AM by Marc-Anthony Di Biase

# Fitching the last number each month

Hi everyone,

I am using now a stupid formula to get the latest information of the day/week/month and wanted to know if someone has a better idea to do it

IF [Time Frame] = "week" THEN

SUM(IF DATENAME('weekday', [Date], 'Monday') = "Sunday" THEN ZN([costs]) ELSE IF [Last Day] THEN ZN([costs]) END END)

ELSEIF [Time Frame] = "month" THEN

SUM(IF DATEPART('day', [Date], 'Monday') = 28 THEN ZN([costs]) ELSE IF [Last Day] THEN ZN([costs]) END END)

ELSE SUM(ZN([costs]))

I am sure it's wrong and there is a better way to do it I just can't think of one at the moment that will prove working better.

Final product: I wish to present a bar chart per month with the costs we had on the last day of this month.

Any help will be appreciated

Thanks

Leo

• ###### 1. Re: Fitching the last number each month

Good morning

the last day for the preceding month can be found with               DATETRUNC("month",[date])-1

the datetrunc formula returns the first day of the month of Date then going back 1 will give you the last day of the preceding month

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Fitching the last number each month

BTW the last day of this month is

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Fitching the last number each month

You may also find a fixed formula helpful depending on how your actual data set is laid out.

{ FIXED month(date) : IF(DAY([Date]) = MAX(DAY([Date])) THEN sum([costs]) END}

This would check if the day of the date is equal to the maximum date recorded (or the end of the month) for each month then summing up the costs.