Grouping by Week Starting Date

[Randyr]

Hello, I am a new Power BI user.  I am looking at Salesforce Live Chat Transcript cases and I am trying to count how many cases were created by week.

I have been able to do this by creating a new Column   Week = WEEKNUM('Live Chat Transcript'[StartTime])

And this new Measure - WeekGroup = CALCULATE(Count('Live Chat Transcript'[CaseId]), ALLEXCEPT('Live Chat Transcript','Live Chat Transcript'[Week]))

This shows me the information by Week 1, Week2, etc. However I want to be able to show  Week Starting ...Jan 1, Jan 8, Jan 15, etc.    

I appreciate any assistance you can provide on how I can do this.

Thanks

[Randyr]

Thank you very much Brett, that new measure is now grouping the dates the way I want.  I am still doing something wrong though, if I add a visual table to look at the data, I group by the new measure I created.   "WeekStarting".  Then I created another measure to count the number of cases like so.

WeekGroup = CALCULATE(COUNT('Live Chat Transcript'[CaseId]),ALLEXCEPT(DateKey,DateKey[WeekStarting].[Date]))

Here is what I see....I think I have the count messed up somehow, it is not summarizing the number of cases by the new measure you gave me, it is totaling them.

Here is the way it looked before.

Thanks again! I appreciate any help you can provide here.

[bjnodello]

Can you check the direction of the date relationship?  I have mine set to "Both".

[Randyr]

Thank you, yes it is set to 'both' now but no change i'm afraid.

[bjnodello]

Can you explain what you are trying to do with the ALLEXCEPT filter?

[Randyr]

Sorry, again I'm new. I was using that filter to group the rows by week. Something I picked up here : http://community.powerbi.com/t5/Desktop/Group-By-Using-Weeks/m-p/30754/highlight/true#M10514  

[bjnodello]

Not a problem, I'm by no means an expert.

Here is what I believe the ALLEXCEPT clause will do.  In the case where you have a date slicer, if you filter by a specific day the count will include the count for the entire week.  If your count does not inlcude the ALLEXCEPT clause your count will only include the records for that specific day.  

For the example below:

WeekGroup = CALCULATE(COUNT('Data'[ID]))

WeekGroup2 = CALCULATE(COUNT('Data'[ID]),ALLEXCEPT(Data,Data[Week]))

There is a single record for January 3 and 4 records between January 2 - 8.

Do you know how you will be filtering and how you want the data to respond?

[Randyr]

Ah, I see. Thank you.

No, I will not require a data slicer for this. I just want to show how many cases were created by week. 

[v-huizhn-msft]

Hi @Randyr,

I create sample data and test, you need to remove ".[Date]" from your formula.

WeekGroup = CALCULATE(COUNT('Live Chat Transcript'[CaseId]),ALLEXCEPT(DateKey,DateKey[WeekStarting]))

Or you also use the following measure, it still return right result.

WeekGrounp11=COUNT('Live Chat Transcript'[CaseId])

Best Regards,
Angelia

[Randyr]

Thank you Angelia, I must be doing something wrong as it is still not working for me.  The grouping looks correct.

Then when I drag the count formula in, it changes to this.

I appreciate any further suggestions you have.

Regards,

Randy

[v-huizhn-msft]

Hi @Randyr,

Do you mind share your .pbix file forfurther analysis?

Angelia

[Randyr · Accepted solution]

Thank you Angelia, I beleive I have it now.

The problem was that I had to create a new column on my date table for "Weeks", then join the "Live Agent Created Date" field to that new column on the Date tab. Once I did that, it is now showing up correctly.

Thanks again for the help!

Randy

[bjnodello]

Welcome Randy,

I generally use a CALENDARAUTO() table (DateData) in my reports.  In this table I create a "Week Starting" column (among others) with the following formula:

Week Starting = IF(WEEKDAY('DateData'[Date],2)=1,'DateData'[Date],DATEADD('DateData'[Date],-WEEKDAY('DateData'[Date],2)+1,DAY))

This gives me the Monday of the current week for each date.  I then join this table to my main data tables with the dates.

Let me know if this helps or you have other questions.

Brett