Create a Date table with last Saturday of each Month as month end/Quarter End

[Anonymous]

Hi,

I am looking to create a Date table with which I can organise my Data by Fiscal Year dates.

Our month end/quarter ends are the last Saturday of each month

Please can anyone advise how to put this together?

Thanks

Chris

[v-xuding-msft]

Hi @Anonymous ,

I think the blogs can help you.

Create a Date Dimension in Power BI in 4 Steps – Step 1: Calendar Columns 

Create a Date Dimension in Power BI in 4 Steps – Step 2: Fiscal Columns 

Create a Date Dimension in Power BI in 4 Steps – Step 3: Public Holidays 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[camargos88]

Hi @Anonymous ,

Try this calculated table:

Last Saturday =

VAR _date = 'Date'[Date]

VAR _LastSaturday = CALCULATE(MAX('Date'[Date]); FILTER(ALL('Date'); YEAR('Date'[Date]) = YEAR(_date) && MONTH( 'Date'[Date]) = MONTH(_date) && WEEKDAY('Date'[Date]) = 7))

RETURN IF(_date = _LastSaturday; 1; 0)

Ricardo

[Mariusz]

Hi @Anonymous 

You can add the below Column, it will return True for the last Sunday of the Month, also if you are looking to return the last Sunday of every Quarter just replace Year Month with Year Quarter column in ALLEXEPT.

Column = 
VAR __lastSunDate =
    CALCULATE(
        MAX( 'Calendar'[Date] ),
        ALLEXCEPT( 'Calendar', 'Calendar'[Year Month] ),
        'Calendar'[Day of Week] = "Sun"
    )
RETURN 
__lastSunDate = 'Calendar'[Date]

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi Mariusz,

Thanks for this, I now have a column which returns True on the last date of each Month/Quarter as you have described.

PLease can you advise how I can now sort my Date Table using this, to differentiate between Quarters, using the True/False Column you have kindly provided?

The desired outcome here is to have my Date table organised by a column Formatted as Q1,Q2,Q3,Q4 - and each quarter ending on the last Sat of each quarter?

Thanks in advance,

Chris