search

sql-bi / DaxDateTemplate

Power BI template to create a Date table in DAX
MIT License
265 stars 76 forks source link

Half Year #66

Open Tealy44 opened 3 years ago

Tealy44 commented 3 years ago

Could fiscal and Calendar half year be included in the date template to assist with half year reporting

So for the calendar half year H1 2021 would be Q1 2021 and q2 2021 of the calendar quater and H2 2021 would be Q3 2021 and Q4 2021.

The same logic for fiscal quaters for the fiscal half year

diegomsg commented 2 years ago

Along with this, it would be helpful for non English models to include prefix configuration variables. Suggestion: VAR WeekPrefix = "W" -- Prefix used in columns of weeks VAR QuarterPrefix = "Q" -- Prefix used in columns of quarters VAR SemesterPrefix = "S" -- Prefix used in columns of semesters/halfs

Personal configuration of the original was made to include semester columns like this, in RETURN block inside VAR CalendarStandardGregorianBase: "Calendar SemesterNumber", CalSemesterNumber, "Calendar Semester", CalendarGregorianPrefix & SemesterPrefix & CalSemesterNumber & " ", "Calendar YearSemesterNumber", CalYear * 2

inside VAR CalendarStandardGregoria: "Calendar StartOfSemester", CalStartOfSemester, "Calendar EndOfSemester", CalEndOfSemester, "Calendar DayOfSemesterNumber", CalDayOfSemesterNumber,

And the VAR Result in the end include the desired columns in the SELECTCOLUMNS function. I didn't made changes in the fiscal calendars.

marcosqlbi commented 2 years ago

While we can modify the content of the column dynamically, we cannot change the column names dynamically. For this reason, there is a translation system in the template so that the DAX code generates the column in English, but they are presented in the translated form in the UI. I'll keep this open to configure the prefixes as you indicated - thanks!