Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
9 months ago
Solved

Calendar table and time intelligence

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

Need Help
Show and Tell
  • Kedar_Pande's avatar
    Kedar_Pande
    9 months ago

    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.

4 Replies

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    9 months ago

    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's avatar
      v-menakakota
      Community Support
      9 months ago

      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's avatar
    amitchandak
    Super User
    9 months ago

    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