Date table grouped by week

[roebern]

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-many relationship. I would like to group my date table by week, resulting in only one row per week. 

How would I go about this?

Thanks in advance!

[v-xiaotang · Accepted solution]

Hi @roebern 

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]

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.