Group By Using Weeks

[usamajadi]

Hi All

I am new in Power Bi.

i want a weekly group by column and count the weekly record sum in another column

Thanks in Advance

[Anonymous]

I have a similar objective, though I need to sum a duration column grouping on the week and on another column. My data looks like this:

From Person	Date	Duration Minutes	Week Number
Person A	2022-01-11	546	3
Perosn B	2022-01-11	534	3
Person C	2022-01-11	477	3
Person A	2022-01-12	485	3
Person B	2022-01-12	500	3
Person C	2022-01-12	540	3
...
Person A	2022-03-30	555	14
Person B	2022-03-30	493	14
Person C	2022-03-30	500	14

Etc.

My objective is to create a visual report table or matrix that shows the summation of the Duration Minutes by week.

I work on my reports and stuff only ever few weeks so I may be forgetting some simple solution.

Thanks again to any who can lend insign and teach me how to do this.

Best Regards

[v-sihou-msft]

Hi @usamajadi,

In this scenario, you need to add a week number column like "Week =WEEKNUM(Table[DateColumn])" as @Greg_Deckler suggested. To calculate weekly count, you need to group the rows using ALLEXCEPT in CALCULATE().

=CALCULATE(COUNT(Table[column]),ALLEXCEPT(Table,Table[WeekNum]))

Reference:
ALLEXCEPT Function (DAX)

Regards,

[Greg_Deckler]

I would create a new column using this formula:

Week =WEEKNUM(Table[DateColumn])

Then I would create a new measure

MyCount = COUNT(Table[SomeColumn])

If you put "Week" column in a table and then your measure "MyCount", the "MyCount" will be filtered based upon the table row and you will have a table of the count of weekly records per week.