Calendar table and time intelligence

[stribor45]

Each of my fiscal year starts at different time.

For example 2022 starts at may 1, 2022, 2023 starts at march 14, 2023, 2024 starts at April 15, 2024 and 2025 starts at May 20, 2025.  

How would I make a calendar table that would take this into consideration.  So for exampke if you running previousyear function and currently as we know is year 2025 it will know that previous year did not start at Jan 1. 2024 but rather at April 15, 2024.

Thanks

[Kedar_Pande · Accepted solution]

Create a custom fiscal calendar table with these columns:

Date

FiscalYear

FiscalPeriod

IsFYStart (flag)

Then use time intelligence functions with your fiscal calendar:

PY Sales =
CALCULATE(
[Sales],
SAMEPERIODLASTYEAR('FiscalCalendar'[Date])
)

Build relationships from your fact table to this fiscal calendar instead of a standard calendar. All time intelligence will then follow your irregular fiscal years.

[v-menakakota]

Hi @stribor45 ,
Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank  @Kedar_Pande , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team 

[amitchandak]

@stribor45 , You can use rank to do that, same can used to know past period 

examples 
COlumn 

PERIOD RANK = RANKX(all("fiscal period"),"fiscal period"[period],,ASC,Dense)

Measure 
YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL("fiscal period"),"fiscal period"[Year]=max("fiscal period"[Year]) && "fiscal period"[period] <= Max("fiscal period"[period]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL("fiscal period"),"fiscal period"[Year]=max("fiscal period"[Year])-1 && "fiscal period"[period] <= Max("fiscal period"[period])))

This period = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[period Rank]=max('Date'[period Rank])))
Last period = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[period Rank]=max('Date'[period Rank])-1))
Last year period= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[period Rank]=(max('Date'[period Rank]) -12)))

Custom(Non Standard) Period Till Date (PTD)- AT 129 -https://youtu.be/rQ3Z_LtxwQM

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

[stribor45]

Could you do a small example by any chance?