Group by week ... Newbie

[Hodgey87]

Hi Guys,

Firstly sorry to be a pain ... I've been asked to produce a dashboard initially for sales per day compared to last year which is working fine. Could I ask what would be the best way to group this table by week number? I.e. Week 1 and total sales, week 2 and total sales. 

Could I use some sort of if statement?

[v-xicai · Accepted solution]

Hi @Hodgey87 ,

You may create measure like DAX below.

Week_Sum=CALCULATE(SUM(Table1[Sales]),FILTER(ALLSELECTED(Table1), YEAR(Table1[date]) =YEAR(MAX(Table1[date]))&&WEEKNUM(Table1[date]) =WEEKNUM(MAX(Table1[date]))))

Best Regards,

Amy 

Community Support Team _ Amy

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

[az38]

@Hodgey87 

sales that you want to sum up are measures or columns?

if columns - you can just aggregate it in visual fields settings

if measures you can try a new measure like

Measure = CALCULATE(SUMX(Table, Table[Sales Variance]), ALLEXCEPT(Table, Table[Week Number]))