datepowerbidax

Last day of calendar quarter in Power BI not capped by table column


I have a table in Power BI called "combined" with a column called "Dates", which begins on 12/31/2003 and ends on 10/28/2024 and will continue to be updated weekly. Within the table, I also have a column called MonthEnd, which identifies the last date of the month based on the Dates column and QuarterEnd, which identifies the last date of the quarter based on the Dates column.

Currently, the MonthEnd column works correctly, because even though the last date in the Dates column is 10/28/2024, it shows 10/31/2024 in the MonthEnd column because I'm using the below formula which looks at the last day of the month on a calendar basis, separate from what's included in the actual dataset.

MonthEnd2 = EOMONTH(combined[Dates], 0)

However, for the QuarterEnd column, I'm using a different formula (because a formula comparable to the monthly formula I used doesn't seem to be available) and the result is therefore wrong because the formula is only including the Dates column and not the entire calendar quarter (and again, the Dates column ends on 10/28/2024.

QuarterEnd = ENDOFQUARTER(combined[Dates])

I'm trying to find the QuarterEnd date of the calendar quarter REGARDLESS of what's in the Dates column. Here's a snippet of the table itself.

Dates MonthEnd2 QuarterEnd Corrected QuarterEnd
9/26/2024 9/30/2024 9/30/2024 9/30/2024
9/27/2024 9/30/2024 9/30/2024 9/30/2024
9/30/2024 9/30/2024 9/30/2024 9/30/2024
10/1/2024 10/31/2024 10/28/2024 12/31/2024
10/2/2024 10/31/2024 10/28/2024 12/31/2024
10/3/2024 10/31/2024 10/28/2024 12/31/2024
10/4/2024 10/31/2024 10/28/2024 12/31/2024
10/7/2024 10/31/2024 10/28/2024 12/31/2024
10/8/2024 10/31/2024 10/28/2024 12/31/2024
10/9/2024 10/31/2024 10/28/2024 12/31/2024
10/10/2024 10/31/2024 10/28/2024 12/31/2024
10/11/2024 10/31/2024 10/28/2024 12/31/2024
10/14/2024 10/31/2024 10/28/2024 12/31/2024
10/15/2024 10/31/2024 10/28/2024 12/31/2024
10/16/2024 10/31/2024 10/28/2024 12/31/2024
10/17/2024 10/31/2024 10/28/2024 12/31/2024
10/18/2024 10/31/2024 10/28/2024 12/31/2024
10/21/2024 10/31/2024 10/28/2024 12/31/2024
10/22/2024 10/31/2024 10/28/2024 12/31/2024
10/23/2024 10/31/2024 10/28/2024 12/31/2024
10/24/2024 10/31/2024 10/28/2024 12/31/2024
10/25/2024 10/31/2024 10/28/2024 12/31/2024
10/28/2024 10/31/2024 10/28/2024 12/31/2024

You can see the issue clearly in the very last row, where the Date column is 10/28/2024, the MonthEnd column is 10/31/2024 (which is correct), the QuarterEnd column is 10/28/2024 (which is incorrect) and the corrected QuarterEnd column is 12/31/2024 (which is correct).

I'm also trying to do this as a calculated column in DAX to facilitate my downstream workflow. Any help is appreciated.

Thanks.


Solution

  • ENDOFQUARTER() is Time Intelligence func, rather than Time func.

    QuarterEnd =
    EOMONTH(
        DATE( YEAR( combined[Dates] ), QUARTER( combined[Dates] ) * 3, 1 ),
        0
    )