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-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'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.

2 Replies

  • 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.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    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.