Forum Discussion
Grouping by Week Across Years
Hi,
I have a model of Term Registration Dates that I want to group by registration week, based on the date registration started for each term. I thought I had it working, except now that I brought more data in that crosses into the next calendar year, the calculation I was using falls apart.
Using the earliest and latest dates available for registration for each term, I was able to generate a table that listed all the dates in between. I then used RANX to rank the dates, filtered for each academic term, to get a registration day number. I didn't use this in the week calculation, but I needed the days as well. For the weeks, I had originally pulled the week number and combined it with the term code which is a numeric value as well. This worked fine until January 1 came along on a Sunday.
The idea of this data is to compare year to year registration by day and week. So if I look at Fall 2021 and Fall 2022, they may have had different registration dates, but I can compare day to day and week to week in the respective period. Following is a very small sample of my data.
GerryL , Assume you have a term start date
//All these column are in a separate date table marked as date table
then you can have columns like
Day of period = datediff([term Start Date],[date], day) +1
Week of period = quotient(datediff([term Start Date],[date], day) ,7) +1 //Based on start Date
or
Week of period =
var _1 = [term Start Date] - weekday([term Start Date],2)+1 //Monday of term start , use 1 for Sunday in weekday
return
quotient(datediff(_1,[date], day) ,7) +1 //Based on start Date
Create rank on term start date
Period Rank = RANKX(all(Period),Period[Term Start Date ],,ASC,Dense)
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))Period Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] =max([Period Day])))
Last period Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] =max([Period Day])))
Period Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Week of period] =max([Week of period ])))
Last period Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Week of period] =max([Week of period ])))
This video can help to understand
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sDAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s
2 Replies
- amitchandak
Super User
GerryL , Assume you have a term start date
//All these column are in a separate date table marked as date table
then you can have columns like
Day of period = datediff([term Start Date],[date], day) +1
Week of period = quotient(datediff([term Start Date],[date], day) ,7) +1 //Based on start Date
or
Week of period =
var _1 = [term Start Date] - weekday([term Start Date],2)+1 //Monday of term start , use 1 for Sunday in weekday
return
quotient(datediff(_1,[date], day) ,7) +1 //Based on start Date
Create rank on term start date
Period Rank = RANKX(all(Period),Period[Term Start Date ],,ASC,Dense)
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))Period Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] =max([Period Day])))
Last period Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] =max([Period Day])))
Period Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Week of period] =max([Week of period ])))
Last period Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Week of period] =max([Week of period ])))
This video can help to understand
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sDAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145sThank you! I actually didn't even need to create the rankings for it to work.
