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.

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.