Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Steps taken so far :
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
Solved! Go to 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.
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.
@v-yetao1-msft , thank you very much for your prompt reply, apologies for not replying earlier, was away on holidays
Please include January data in your sample.
User | Count |
---|---|
96 | |
87 | |
78 | |
73 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |