Forum Discussion
Grouping by Week Starting Date
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
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
12 Replies
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
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.
