topic Re: DAX - Date Table - Periods/Months in DAX Commands and Tips

DAX - Date Table - Periods/Months

Anonymous — Thu, 03 Aug 2023 15:12:35 GMT

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.

Re: DAX - Date Table - Periods/Months

ERD — Thu, 03 Aug 2023 19:26:25 GMT

Hi,

Try these sources:

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

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

Re: DAX - Date Table - Periods/Months

Anonymous — Tue, 08 Aug 2023 02:37:46 GMT

Hi Anonymous ,

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.