Forum Discussion

GerryL's avatar
GerryL
Frequent Visitor
4 years ago
Solved

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 brough...
  • amitchandak's avatar
    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=145s

     

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