Custom Calendar fiscal year october
i want to create a day number on DAX Calendar. we have a custom calendar for fiscal year, our fiscal year stars on october 1 and i want to create a column when day #1 are october first, 2 october second... could you please help?, i alredy try trhu date dif: daynum#FIX = DATEDIFF ( DATE ( YEAR ('Calendario End Date'[Date]),10,1 ), 'Calendario End Date'[Date], DAY ) + 1 but when function comes to 1jan retun -273 thanks in advance for the helpDAX Cookbook "Determine day and working day numbers"
Hi everyone, hi Greg, I got your book "Dax cookbook" and am going to go through it, all 520 pages (great book, love it! I failed on the Italian's "Definitive DAX Guide"). I am not totally new with PBI and DAX, around 2 years of experience, but complex DAX is still hard stuff for me, so as this challenge. I went to the mentioned chapter (page 55), got it going but I am not sure about the result. I would expect the outcome should be the count of ONLY the working days of a calendar year, ignoring Sat and Sun (means weekday([date],2) <6), bc I am from Germany), which totals to 262 days in 2020. Then the calculation should repeat for 2021 giving the number of workdays for that year and so on. I realized the first part with the following DAX. _Workdaycheck = if(WEEKDAY(R04_Calendar[Date],2) <6,1,0) _Workdayearlier = SUMX(FILTER(R04_Calendar,'R04_Calendar'[Date]<=EARLIER('R04_Calendar'[Date])),R04_Calendar[_Workdaycheck]) This gives me the count of the working days ignoring Sat and Sun in the calendar and works perfectly for one year e.g. I filter the table to 2020. But I could not solve the problem, that it keeps counting over the 31/12/2020, while it actually should restart counting on Jan 1st. Expected result date weekday weekdaycheck working days 26/12/2020 Sat 0 257 27/12/2020 Sun 0 258 28/12/2020 Mon 1 259 29/12/2020 Tue 1 260 30/12/2020 Wed 1 261 31/12/2020 Thr 1 262 01/01/2021 Fri 1 1 02/01/2021 Sat 0 03/01/2021 Sun 0 04/01/2021 Mon 1 2 05/01/2021 Tue 1 3 The result in the book looks much different, so I am wondering, whether I am doing something wrong and how I can solve the problem to restart the counting cycle. Any idea is highly appreciated. Greg_Deckler See you guys BenediktDate Table - DAX - CALENDAR() - ADDCOLUMNS() - Quarter Issue
Hi, I create a new Date table with DAX using the CALENDAR() function. Adding new columns, no problem. When I add a new "Quarter" colum into the table my table stops starting from Jan 1st and move to start from Jul 1st. I can't understand why. Any help greatly appreciated. First Variant (correct) Date = VAR MinDate = DATE(2016,1,1) VAR MaxDate = DATE(2021,12,31) RETURN ADDCOLUMNS ( CALENDAR(MinDate, MaxDate), "Calendar Year", "CY " & YEAR ([Date]) ) Result Second Variant (wrong) Date = VAR MinDate = DATE(2016,1,1) VAR MaxDate = DATE(2021,12,31) RETURN ADDCOLUMNS ( CALENDAR(MinDate, MaxDate), "Calendar Year", "CY " & YEAR ([Date]), "Calendar Quarter", "CQ " & QUARTER([Date]) ) Result Where I am wrong? Thanks
