Hi Sir, Great video.... 🤟 Could you pls make a tutorial video where if user select a date like 9/10/21...then user can see the sales of 9/10/20, 9/10/19, 9/10/18 so far so on...like yearly difference..same can be modified to monthly and quarterly difference as well...
Thanks for this video. I wanna ask a question as my company uses a different calenders system where week starts on Saturdays, march, June, Sept and Dec has 5 weeks each while the other months, 4 weeks. How do I create a calendar table with these parameters?
The default functions in DAX (such as Weekday) can only consider Sunday or Monday as the start of the week. If your week starts from Saturday, then you have to do the calculation with some other functions and calculate the difference of each day with the previous Saturday. DateDiff and some other functions can be helpful
Best video that clears all doubts on the Date Table.. I'm glad I found it at the right time and clear my ambiguity.. Thanks a lot 😍
I prefer Date table created by DAX. it is simply to create, maintain, reuse
Mr RADACA, I am having issue in getting the same period sales from my 445 calendar
Hi Sir,
Great video.... 🤟
Could you pls make a tutorial video where if user select a date like 9/10/21...then user can see the sales of 9/10/20, 9/10/19, 9/10/18 so far so on...like yearly difference..same can be modified to monthly and quarterly difference as well...
Thanks for this video. I wanna ask a question as my company uses a different calenders system where week starts on Saturdays, march, June, Sept and Dec has 5 weeks each while the other months, 4 weeks. How do I create a calendar table with these parameters?
The default functions in DAX (such as Weekday) can only consider Sunday or Monday as the start of the week. If your week starts from Saturday, then you have to do the calculation with some other functions and calculate the difference of each day with the previous Saturday. DateDiff and some other functions can be helpful
New MTD Sales LY =
VAR CurrentDate = MAX(V_Calendar[SetDat])
VAR CurWrkDay = MAX(V_Calendar[MonthlyWrkDay])
VAR RepMonth = MAX(V_Calendar[CokeMonth])
VAR CurYear = YEAR(CurrentDate)
VAR PrvYear = CurYear-1
VAR TotalWrkDayInCYMonth = MAXX(FILTER(ALL(V_Calendar) , V_Calendar[CokeYear]=CurYear && V_Calendar[CokeMonth]=RepMonth),V_Calendar[MonthlyWrkDayNum])
VAR TotalWrkDayInPYMonth = MAXX(FILTER(ALL(V_Calendar) , V_Calendar[CokeYear]=PrvYear && V_Calendar[CokeMonth]=RepMonth),V_Calendar[MonthlyWrkDayNum])
VAR RepDay = IF((CurWrkDay>=TotalWrkDayInPYMonth), TotalWrkDayInPYMonth, CurWrkDay)
VAR mSartOfMonth = MINX(FILTER(ALL(V_Calendar) , V_Calendar[CokeYear]=PrvYear && V_Calendar[CokeMonth]=RepMonth),V_Calendar[SetDat])
VAR mEndOfMonth = MAXX(FILTER(ALL(V_Calendar) , V_Calendar[CokeYear]=PrvYear && V_Calendar[CokeMonth]=RepMonth && V_Calendar[MonthlyWrkDayNum] = mSartOfMonth && V_Calendar[SetDat]