DAX time comparison and identification of unique entries

[Anonymous]

Hello Community,

Hope everyone is well and safe.

I would appreciate it if you could help me with the below queries.

Description : I receive a report on a monthly basis. This report captures errors in Master Data set ups. The report's template includes the error description , focal product , the route details (from and to locations) and extraction date. January's report is considered to be the baseline.

Scope : I want to compute the below:

1. Delta in terms of number of errors between current month and baseline
2. Delta in terms of number of errors between current month and previous month
3. Unique products with errors, appearing only in current report (not part of all combined previous reports)
4. Unique combos of product+from+to+error, appearing only in current report (not part of all combined previous reports)

Steps taken so far :

1. I created a dates table and built my model
2. For points 1 and 2 , I built DAX functions counting the number of errors per specific month and then using these functions to work on the deductions. This means that I have to create such a new measure to capture the counts of the new report and to work on the various deductions, every time a new report is provided.
3. For points 3 and 4 , I used power queries and merged tables to identify the unique entries. Once the new report is provided, I then have to move reports around to various folders , in order to be able to distinquish between new report and accumulated old ones.

Although I have managed to eventually produce a dashboard, nevertheless I want to build some DAX measures that will move me away from having to continuously take the above actions on a monthly basis.

Below you can see a small example of the dataset I receive.

Product	From Location	To Location	Error Code	Extraction Date
Prod1	FromLoc01	ToLoc01	ErrCd01	07/01/2021
Prod2	FromLoc02	ToLoc02	ErrCd02	07/01/2021
Prod3	FromLoc03	ToLoc03	ErrCd03	07/01/2021
Prod4	FromLoc04	ToLoc04	ErrCd04	07/01/2021
Prod5	FromLoc05	ToLoc05	ErrCd05	07/01/2021
Prod1	FromLoc01	ToLoc01	ErrCd01	09/02/2021
Prod2	FromLoc02	ToLoc02	ErrCd02	09/02/2021
Prod3	FromLoc03	ToLoc03	ErrCd03	09/02/2021
Prod1	FromLoc01	ToLoc01	ErrCd01	06/03/2021
Prod2	FromLoc02	ToLoc18	ErrCd02	06/03/2021
Prod3	FromLoc03	ToLoc03	ErrCd18	06/03/2021
Prod6	FromLoc06	ToLoc06	ErrCd06	06/03/2021

Any help would be utterly appreciated.

Kindest regards,

George

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

You can create a Calendar Date table as a slicer to filter data in your data table .

Calendar Date = CALENDAR(DATE(2021,01,01),DATE(2021,03,31))

Then calculate the numbers for current month error , previous month error and baseline month error separately,

current error number = CALCULATE(COUNT('Table'[Error Code]),FILTER('Table','Table'[Extraction Date].[MonthNo]=SELECTEDVALUE('Calendar Date'[Date].[MonthNo])))

previous error number = CALCULATE(COUNT('Table'[Error Code]),FILTER('Table','Table'[Extraction Date].[MonthNo]=SELECTEDVALUE('Calendar Date'[Date].[MonthNo])-1))

baseline error number = CALCULATE(COUNT('Table'[Error Code]),FILTER('Table','Table'[Extraction Date].[MonthNo]=1))

Finally to calculate the diff between current and previous ,current and baseline .

current-baseline = [current error number]-[baseline error number]

current-previous = [current error number]-[previous error number]

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yetao1-msft  , thank you very much for your prompt reply, apologies for not replying earlier, was away on holidays

[lbendlin]

Please include January data in your sample.