Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date table grouped by week

Hi all,    I'm working with facts-table which doesn't have a date column but instead has a calendar year/week column (yyyyww). My date table has this column as well but this results in a many-to-ma...
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    Thanks for reaching out to us.

    >>  I would like to group my date table by week, resulting in only one row per week. 

    You can try this,

    OnlyOneRow_PerWeek = 
var _maxPerW=CALCULATE(MAX('date'[Date]),FILTER(ALL('date'),'date'[YW]=EARLIER('date'[YW])))
return IF('date'[Date]=_maxPerW,'date'[YW])

    result

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

You could try creating a bridge table like

Year Week Table = ALLNOBLANKROW('Date'[Year Week])

and then creating relationships from the new table to your fact table and your date table. Make the relationship with the date table bidirectional.