Displays the last date of each month

[ExMachina]

Good day everyone.

In the summary table, I made an output of the last date of the month and, accordingly, the output of data for the specified date. I also want to output the last current date, but I don't understand which one to implement.

If(floor(DATE) = floor(Monthend(max(DATE))), If(floor(DATE) = floor(max(DATE)), date(max(DATE)), date(DATE))) as DateEnd

[Taoufiq_Zarra]

Hi,

can you develop or give an example

[ExMachina]

I want to see something like this.

I hope this is the right example.

[aapurva09]

There is a difference between using Monthend(max(Date)) and max(Date).

MonthEnd(max(date)): Gives you the month end date from the Month the Date belongs to. Date= 10.01.2020 then outcome 31.01.2020.

Max(Date): Gives you the maximum date available for your unique values used for grouping the table. for exp. Date has 2 values 10.01.2020 and 20.01.2020 for the unique record, output will be 20.01.2020.

So, in your case monthend is needed.

Thanks

[ExMachina]

Thank you for the explanation. 

But in my case, I do not know how to correctly paint the condition so that the last dates of the completed months and the last date of the current month are displayed, as in the screenshot above. When using MonthEnd, I only get what is shown in the first message.

[ThePeterK]

I would use an if statement in my data load. If the month & year of the value in your date field are the same month & year as today, then use the maxdate, else use the monthend of the value in the date field.

[ExMachina]

Thanks for your advice

I tried using this formula:

If(Month(DATE)=Month(Today()) and Year(DATE)=Year(Today()), Date(Floor(max(DATE))), If(Floor(DATE)=Floor(Monthend(DATE)), date(DATE)))as DateEnd;

Аll the last months are displayed,as well as all the dates of the current month. Can you tell me what to correct in the condition to exclude the output of all dates for the current month?