Please Help. WeekNum to Pay Period column/Formula
l'm creating a workbook for tracking meetings. I've just generated a pivot table calendar from a sheet with the following columns: "Date, WeekNum, WeekDay, Day, Month." It's great! However, I desperately need either the week number to align with the pay period calendar (starting on XX/XX/YR instead of 01/01/YR), or I need a column that is going to show the pay period week formulated off of the week number- but starting over at the end of the pay period year (So it can't just be a basic cell/value+#). Also, I'm COMPLETELY new to this, so I'm way in over my head with the formulas- but work is work. So, basically explain step by step what to do, or else I'll be lost. Thanks!
Week over Week sales calculation
I am trying to figure out week over week calculation. I searched online and found DAX measure. I created columns for Year, WeekDay, and WeekNum. what I am using is like below: WoW Rev OTB = SUM ( 'Table'[sales on the book] ) - CALCULATE ( SUM ( 'Table'[sales on the book] ), FILTER ( ALL ( 'table' ), 'table'[Year] = MAX ( 'table'[Year] ) && 'table'[WeekNumber] = MAX ('table'[WeekNumber]) - 1 && 'table'[WeekDay] = MAX ( 'table'[WeekDay] ) ) ) The result turned very well, I got the total number for week over week sales. However, when I tried to apply on different region or destinations or choose other status, the week over week sales numbers showed all negative numbers which doesn't seem correct. (This is the screenshot for WoW sales without any filters) (After I selected "Established" in status slicer, all the numbers turn into negative. Same thing would happen if I selected product market slicer.) Can anyone help me how to apply week over week sales on categories? Thank you in advance!
M code for a DAX expression
Hi everyone, I need to transform this DAX expression in M: Week= YEAR(Calendar[Date]+26-WEEKNUM(Calendar[Date],21)) & "-" & IF(WEEKNUM(Calendar[Date],21)>=10, WEEKNUM(Calendar[Date],21), "0" & WEEKNUM(Calendar[Date],21)) which calculates the week of the year followed by the year for each date of my calendar, according to the European system: Can you help me? Many thanks!Function Calendar Wrong WeekNum
Hi there, I have a date table with the following DAX-Code: Dates = CALENDAR(DATE(2018,01,01),DATE(2030,12,31)) According to the documentation, the WeekNum function works like this: "By default, the WEEKNUM function uses a calendar convention in which the week containing January 1 is considered to be the first week of the year. However, the ISO 8601 calendar standard, widely used in Europe, defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition." For the year 2021 happens exactly this case. There are 1.1. - 3.1. in the week 53 of the year 2020. But the the WEEKNUM-Functions shows this three days as the week no. 1 of the year 2021. The result, the WeekNum numbers don't match with the no. in europe. How can I fix mismatch? Thanks for help Cheers Mike_CH
FISCAL QUARTER WEEK CALCULATIONS - CONFUSED
Hi guys, I developed a calculated column to group dates within a Fiscal Quarter into their respective Week Groupings within the fiscal quarter. The measure works perfectly for FQ1,FQ2,FQ3 but not for FQ4 which I believe is because the calculation is confused by my fiscal periods ( Fiscal Year Begins in Feb) Here is my measure : VAR Q = 'DATE'[FiscalQuarter] VAR Y = 'DATE'[FiscalYear] VAR FYMONTH = 'DATE'[FiscalMonthNum] VAR MONTH1 = FORMAT('DATE'[Date],"MMM") VAR WEEKNUM1 = RANKX(FILTER('DATE','DATE'[FiscalQuarter] = Q && 'DATE'[FiscalYear] = Y ), WEEKNUM('DATE'[Date],2),,ASC,Dense) VAR WEEKNUMBERS = IF('DATE'[QUARTER START DAY] <> "Mon" && WEEKNUM1 = 1,1,IF('DATE'[QUARTER START DAY] <> "Mon",WEEKNUM1-1,WEEKNUM1)) RETURN WEEKNUM1 Here are my skewed reuslts for Q4 : As you can see FQ4 should begin in November but the weeks start ascending in January, then November and finally December but the tru order should be November, December and then January. Any idea how to fix my formula to take into account my fiscal period?
