Grouping by Week Across Years

[GerryL]

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.

[amitchandak · Accepted solution]

@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=145s

DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

[GerryL]

Thank you! I actually didn't even need to create the rankings for it to work.