Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
88 | |
81 | |
60 | |
60 | |
58 |
User | Count |
---|---|
157 | |
118 | |
103 | |
76 | |
66 |