Forum Discussion

Randyr's avatar
Randyr
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • Randyr's avatar
    Randyr
    9 years ago

    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's avatar
bjnodello
Helper II
9 years ago

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's avatar
Randyr
Frequent Visitor
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
    Randyr
    Frequent Visitor
    9 years ago

    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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi @Randyr,

    Do you mind share your .pbix file forfurther analysis?

    Angelia

  • Randyr's avatar
    Randyr
    Frequent Visitor
    9 years ago

    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