DAX - Date Table - Periods/Months

[MT123456]

Hello all, 

In my model with multiple tables (sales, orders, finance), I have a calendar table with date/week/month/quarter/year and financial week/period/financial year. 

Week and financial week, month and period, year and financial year don't match. 

I need to work with the financial week/period/financial year and compare this financial year to previous ones. Could you please suggest the best way to do it? 

Thank you. 

[Anonymous · Accepted solution]

Hi @MT123456 ,

Please refer to.

Financial Week =
VAR FirstDayOfYear =
    DATE ( YEAR ( [Date] ), 1, 1 )
VAR FiscalYearStart =
    IF (
        MONTH ( FirstDayOfYear ) >= 4,
        FirstDayOfYear,
        FirstDayOfYear - DAY ( FirstDayOfYear ) + 1 + 92
    )
VAR FiscalWeekStart =
    FiscalYearStart
        + ( WEEKDAY ( FiscalYearStart ) - 1 ) * -1
RETURN
    CEILING ( DIVIDE ( [Date] - FiscalWeekStart, 7 ), 1 )

Sales This Fiscal Year = CALCULATE(SUM(Sales[Amount]), FILTER(Calendar, Calendar[Financial Year] = MAX(Calendar[Financial Year])))

Sales Last Fiscal Year = CALCULATE(SUM(Sales[Amount]), FILTER(Calendar, Calendar[Financial Year] = MAX(Calendar[Financial Year]) - 1))

Fiscal Year Sales Growth = DIVIDE([Sales This Fiscal Year] - [Sales Last Fiscal Year], [Sales Last Fiscal Year])

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[ERD]

Hi,

Try these sources:

https://www.sqlbi.com/articles/sorting-months-in-fiscal-calendars/

https://www.daxpatterns.com/custom-time-related-calculations/