Calendar table with custom column -> first day of year on Saturday

[mihaigm]

Hello everyone!!!

I need to create a calculated Data Table in DAX that has also a "custom date" column that always starts the new year with the last Saturday of the previous year.

Eg:

- Dicember 30th 2023 (real date) = January 1st 2024 (custom date).

- Dicember 28th 2024 (real date) = January 1st 2025 (custom date).

So, I've  already created the standard calendar table in DAX, but I'm having trouble in creating the "custom date" column..

Any idea how to do it? Thank you so much!

[bhanu_gautam]

@mihaigm , Try using this to create new column

CustomCalendar =
ADDCOLUMNS (
Calendar,
"CustomDate",
VAR CurrentYear = YEAR([Date])
VAR LastSaturdayOfPrevYear =
CALCULATE (
MAX ( Calendar[Date] ),
Calendar[Year] = CurrentYear - 1,
Calendar[Weekday] = 6 -- 6 means Saturday
)
VAR DaysSinceLastSaturday = DATEDIFF ( LastSaturdayOfPrevYear, [Date], DAY )
RETURN
IF (
[Date] >= LastSaturdayOfPrevYear,
DATE ( CurrentYear, 1, 1 ) + DaysSinceLastSaturday,
DATE ( CurrentYear - 1, 1, 1 ) + DaysSinceLastSaturday
)
)

[mihaigm]

Thank you so very much @bhanu_gautam for your quick answer!!! But, unfortunately still returns an error... could you have a simple Date Table code also with the "custom column" in it??

[Anonymous · Accepted solution]

Hi All,
Firstly  bhanu_gautam thank you for your solution!

And @mihaigm ,According to your needs, you want to customize a new calculated column to achieve your needs, right?
Then you can try the code, hope it helps you:

Custom Date = 
VAR CurrDate = 'Calendar'[Date]
VAR CurrYear = YEAR(CurrDate)
VAR EndOfYearCurrent = DATE(CurrYear, 12, 31)
VAR EndOfYearPrev = DATE(CurrYear - 1, 12, 31)
VAR LastSatCurrent =
    EndOfYearCurrent - MOD(WEEKDAY(EndOfYearCurrent) - 7, 7)
VAR LastSatPrev =
    EndOfYearPrev - MOD(WEEKDAY(EndOfYearPrev) - 7, 7)
VAR CustomYear =
    IF(CurrDate >= LastSatCurrent, CurrYear + 1, CurrYear)
VAR CustomStart =
    IF(CurrDate >= LastSatCurrent, LastSatCurrent, LastSatPrev)
RETURN
    DATE(CustomYear, 1, 1) + (CurrDate - CustomStart)

If you have any other questions, you can check out the pbix file I uploaded, I hope it helps, and I'd be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[mihaigm]

Hello @Anonymous and thank you so much for your solution!!! I've tested it a little bit yesterday and seems working perfectly!!! I hope also would help anyone else having the same problem! Wish you a wonderful day!!!!