Weekly average of a string column based on category instances

[MuthalibAbdul]

So I have a table with patient name, which physician has seen and when. I need to be able to count the number of patients seen per physician (which is achievable by simply using a table). However, now I need to do a weekly average of this number. 

Furthermore, if a physician has joined midway say June 2nd week, I want the average to start calculating from then moving forward. Otherwise the average would assume a 0 for weeks before the physician even joined the company. 

Please let me know if a sample data set would help. Thanks

[danextian]

Hi @MuthalibAbdul 

Aside from the sample data, how do you determine which date belongs to which week?

[MuthalibAbdul]

if not please see below snapshot

[MuthalibAbdul]

https://drive.google.com/drive/folders/1thOs4dn7vDKtqWD_1Z4mGpXeyE3XsKEh?usp=sharing

Check sample data and dashboard, We have date of service when the patient was seen. I create a calender table and link the dates in both tables to get me my weekly numbers

[rajendraongole1]

Hi @MuthalibAbdul  -Can you please share sample data as well with expected output, it helps to analyze and share the result quickly.   as per above information, you need to create DAX logics to build the same. . 

[MuthalibAbdul]

[Ashish_Mathur]

Hi,

In the Calendar table, create a week number column.  To your Matrix visual, drag Year and week number to the column labels and Physician to the Row labels.  Write this measure

Measure = counta(Data[Physician])

Hope this helps.

[MuthalibAbdul]

https://drive.google.com/drive/folders/1thOs4dn7vDKtqWD_1Z4mGpXeyE3XsKEh?usp=sharing

Please let me know if you have access to this excel and pbix file