Help! I have rubbish data!

[Anonymous]

Hello all,

I don't even konow how to title this request, and I can't formulate a question to even search on, so sorry for this. 

What it is, is that I get so much data in Excel form that conforms to this standard, as it is easier to track projects like this. The jobs are all listed out, then as someone completes them, they put the date in and their name.  However, I need to be able to convert that into nice visaualisations to show how many have been done by a particular person in a particular week. So it is not just getting running totals for the dates, but linking that to each person and how many they have done each week. Plus it is not just the latest week, so I cannot just filter the last seven days and count each name. I need to show each week for each person.   Here is some sample data.

Job		Completed		By
Job 1		22/10/2020		Tom
Job 2		11/11/2020		**bleep**
Job 3
Job 4		14/10/2020		Harry
Job 5
Job 6
Job 7		30/10/2020		**bleep**
Job 8		06/11/2020		Tom
Job 9		29/09/2020		Tom
Job 10		14/10/2020		Harry
Job 11		03/11/2020		Tom
Job 12
Job 13		19/11/2020		Harry
Job 14		14/09/2020		**bleep**
Job 15
Job 16
Job 17
Job 18		01/10/2020		Tom
Job 19		02/11/2020		Harry
Job 20		27/10/2020		Harry
Job 21		01/11/2020		**bleep**
Job 22		02/10/2020		Tom
Job 23
Job 24		01/10/2020		Tom
Job 25		02/11/2020		**bleep**
Job 26		27/10/2020		Harry
Job 27		01/11/2020		Harry
Job 28		02/10/2020		**bleep**
Job 29		27/10/2020		Tom

I would be very grateful for any suggestions.

P.S. MS are being very PC friendly here,  **bleep** = a shortend version of Richard 😉 

[v-shex-msft]

HI @Anonymous,

Did these 'job id' duplicates on your table records? If this is a case, you can consider creating a table visual with 'job id' and logged user name as category and count of 'job id' fields.

It should summarize these records based on the 'job id' and 'user name' groups.
Then you can use the completed date field as the source of the slicer to filter specific date ranges of records and setting a 'value level filter' on table visual to remove records with blank computer date/user.
Regards,

Xiaoxin Sheng

[edhans · Accepted solution]

Hi @Anonymous - You need a data table to do this.

The DAX is simple. It ignores any data with no completed date, otherwise, it counts by the user within the week. This date table is set to start the weeks on Sunday, but you can change it per the directions here. 
Here is my PBIX file so you can see the whole thing.

Count of Data = 
IF(
    ISBLANK(MAX('Data Table'[Completed])),
    BLANK(),
    COUNTROWS('Data Table')
)

I recommend a date table in Power Query when possible, and it seems to be in this case. The link above gives you the quick copy/paste version for Power Query and you can easily change it to suit your needs.

[mahoneypat]

Lauged at loud at the explanation of **bleep**.  Your data are not rubbish.  In fact, they look perfectly formatted for analysis.  You just need to add a simple calendar table to your model with columns for week, month, year, etc. and make a relationship between it and your Completed column.  Then you can start making all the visuals you need.

Creating a simple date table in DAX - SQLBI

Regards,

Pat

[MFelix]

Hi @Anonymous ,

Create a calendar table and make a relationship with the complete date now use the values to make your visualization has you need.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/