Forum Discussion
Grouping by Week Across Years
- 4 years ago
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
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
Thank you! I actually didn't even need to create the rankings for it to work.