cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Custom Calendar Best Practice - Custom Hierarchy Sort By

Good morning,
I've developed a custom calendar that groups by a given WeekNum on the month, based on how many working days of the belonging Week belong to the month, even if it appeared to me that the calculation process seems over-complicated

``````TargetMonth_Algorithm =

SWITCH(TRUE(),

'Calendar'[Working Days in Week]='Calendar'[Max Working Day Week] , MONTH('Calendar'[Date]),
'Calendar'[Working Days in Week]<'Calendar'[Max Working Day Week] && [IsMaxWeekNum]=1, MONTH('Calendar'[Date])+1,
'Calendar'[Working Days in Week]<'Calendar'[Max Working Day Week] && [IsMinWeekNum]=1, MONTH('Calendar'[Date])-1,
'Calendar'[Working Days in Week]>'Calendar'[Max Working Day Week] && [IsMinWeekNum]=1, MONTH('Calendar'[Date]),
'Calendar'[Working Days in Week]>'Calendar'[Max Working Day Week] && [IsMinWeekNum]=0, MONTH('Calendar'[Date])-1,

MONTH('Calendar'[Date])+1)``````

In other words somehow, I managed to carry out the prementioned;
Now I have the following problem, I've created a custom hierarchy with :
Year -> TargetMonth (which is the algorithm month format "mmmm") --> weeknum
But unluckily I cannot display with the sorting that I would like.

I could concat MonthNum & MonthName and the sort would be working, but I would just like to sort the hierarchy based on the MonthNum column without including it in the hierarchy.

Best Regards

1 ACCEPTED SOLUTION
Super User

In general you want to consider using an external calendar table, rather than wasting your time trying to do this in DAX.

Super User

In general you want to consider using an external calendar table, rather than wasting your time trying to do this in DAX.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.