Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to create a matrix that shows several variables with columns for the current month, YTD this year, YTD last year, and then the difference between YTD this year and last year. These columns can be selected from a slicer on the side. What I want is how to calculate the difference between YTD based on multiple selected values. I want to calculate the (AD-OEE) from the selected YTD of the larger year minus the (AD-OEE) selected YTD of the smaller year. If you look at the attachment (pic1), I want to replace 'Jan21' with 'YoY' value. I found this post but when I tried, I didn't get what I wanted
https://community.fabric.microsoft.com/t5/Desktop/Difference-between-two-columns-or-rows/td-p/188185
Difference = IF ( HASONEVALUE ( 'Table'[YEAR] ), BLANK (), CALCULATE ( SUM ( 'Table'[AMOUNT] ), FILTER ( 'Table', 'Table'[YEAR] = MAX ( 'Table'[YEAR] ) ) ) - CALCULATE ( SUM ( 'Table'[AMOUNT] ), FILTER ( 'Table', 'Table'[YEAR] = MIN ( 'Table'[YEAR] ) ) ) )
Oh, currently, I am using 3 overlapped matrices. Is there any advice to make it more visually effective but with the same goal? Pic2 is what I want to create. Can anyone help me out?
(pic1)
(pic2)
Data that I used (dummy)
Period | Period Information | AD | ACD | DT | AU | IDT | SDT | NDT | NRL | AP | RR | OE | OEE |
Jan 21 | MTD Jan 21 | 29 | 29 | 24 | 26 | 31 | 30 | 30 | 25 | 28 | 72 | 10 | 76 |
Feb 21 | MTD Feb 21 | 28 | 22 | 25 | 5 | 26 | 28 | 0 | 0 | 29 | 70 | 55 | 95 |
Mar 21 | MTD Mar 21 | 30 | 21 | 21 | 19 | 25 | 13 | 1 | 26 | 4 | 36 | 8 | 85 |
Apr 21 | MTD Apr 21 | 30 | 29 | 27 | 13 | 25 | 31 | 24 | 6 | 13 | 49 | 47 | 34 |
May 21 | MTD May 21 | 29 | 25 | 24 | 40 | 29 | 17 | 25 | 13 | 11 | 67 | 31 | 53 |
Jun 21 | MTD Jun 21 | 30 | 31 | 31 | 36 | 3 | 5 | 13 | 18 | 21 | 98 | 6 | 1 |
Jul 21 | MTD Jul 21 | 28 | 29 | 29 | 94 | 30 | 29 | 2 | 16 | 23 | 67 | 50 | 91 |
Aug 21 | MTD Aug 21 | 30 | 29 | 20 | 32 | 27 | 29 | 26 | 2 | 0 | 23 | 29 | 19 |
Sep 21 | MTD Sep 21 | 29 | 27 | 27 | 9 | 24 | 25 | 25 | 2 | 7 | 72 | 11 | 72 |
Oct 21 | MTD Oct 21 | 28 | 26 | 31 | 73 | 14 | 24 | 22 | 1 | 8 | 64 | 53 | 37 |
Nov 21 | MTD Nov 21 | 29 | 23 | 23 | 73 | 30 | 30 | 25 | 12 | 31 | 11 | 93 | 3 |
Dec 21 | MTD Dec 21 | 28 | 30 | 21 | 60 | 9 | 0 | 28 | 28 | 6 | 6 | 14 | 17 |
Dec 21 | YTD 2021 | 348 | 321 | 303 | 480 | 273 | 261 | 221 | 149 | 181 | 635 | 407 | 583 |
Jan 22 | MTD Jan 22 | 30 | 31 | 28 | 68 | 0 | 14 | 22 | 0 | 8 | 25 | 18 | 59 |
Feb 22 | MTD Feb 22 | 28 | 29 | 28 | 55 | 22 | 16 | 28 | 11 | 11 | 32 | 11 | 76 |
Mar 22 | MTD Mar 22 | 31 | 25 | 21 | 59 | 13 | 13 | 24 | 13 | 30 | 29 | 23 | 6 |
Apr 22 | MTD Apr 22 | 31 | 30 | 27 | 21 | 0 | 26 | 24 | 15 | 16 | 73 | 88 | 4 |
May 22 | MTD May 22 | 29 | 22 | 24 | 46 | 3 | 4 | 2 | 8 | 6 | 33 | 63 | 97 |
Jun 22 | MTD Jun 22 | 28 | 20 | 25 | 79 | 10 | 24 | 8 | 26 | 7 | 69 | 88 | 13 |
Jul 22 | MTD Jul 22 | 28 | 25 | 23 | 16 | 10 | 7 | 16 | 5 | 2 | 86 | 86 | 33 |
Aug 22 | MTD Aug 22 | 31 | 25 | 28 | 46 | 24 | 29 | 2 | 29 | 28 | 71 | 70 | 2 |
Sep 22 | MTD Sep 22 | 29 | 28 | 22 | 14 | 1 | 22 | 1 | 28 | 5 | 20 | 60 | 55 |
Oct 22 | MTD Oct 22 | 30 | 21 | 24 | 40 | 21 | 22 | 6 | 1 | 15 | 1 | 28 | 31 |
Nov 22 | MTD Nov 22 | 30 | 29 | 25 | 63 | 24 | 14 | 15 | 13 | 21 | 67 | 84 | 33 |
Dec 22 | MTD Dec 22 | 31 | 25 | 22 | 72 | 30 | 6 | 11 | 10 | 13 | 84 | 82 | 60 |
Dec 22 | YTD 2022 | 356 | 310 | 297 | 579 | 158 | 197 | 159 | 159 | 162 | 590 | 701 | 469 |
Jan 23 | MTD Jan 23 | 29 | 20 | 21 | 89 | 8 | 13 | 10 | 8 | 19 | 47 | 15 | 23 |
Feb 23 | MTD Feb 23 | 29 | 23 | 30 | 30 | 24 | 9 | 9 | 22 | 31 | 56 | 6 | 65 |
Mar 23 | MTD Mar 23 | 28 | 27 | 29 | 99 | 21 | 22 | 8 | 18 | 12 | 52 | 74 | 84 |
Apr 23 | MTD Apr 23 | 28 | 22 | 25 | 70 | 8 | 17 | 2 | 22 | 18 | 89 | 17 | 47 |
May 23 | MTD May 23 | 29 | 24 | 30 | 97 | 0 | 10 | 2 | 12 | 19 | 64 | 4 | 17 |
Jun 23 | MTD Jun 23 | 28 | 25 | 30 | 13 | 25 | 13 | 27 | 18 | 24 | 20 | 47 | 13 |
Jul 23 | MTD Jul 23 | 30 | 26 | 20 | 42 | 26 | 6 | 26 | 1 | 5 | 13 | 44 | 100 |
Aug 23 | MTD Aug 23 | 30 | 30 | 28 | 73 | 19 | 9 | 13 | 27 | 15 | 45 | 94 | 96 |
Sep 23 | MTD Sep 23 | 30 | 30 | 30 | 22 | 4 | 17 | 24 | 19 | 15 | 61 | 36 | 96 |
Oct 23 | MTD Oct 23 | 31 | 22 | 23 | 62 | 29 | 24 | 6 | 23 | 26 | 16 | 72 | 76 |
Nov 23 | MTD Nov 23 | 30 | 29 | 21 | 36 | 17 | 0 | 24 | 22 | 6 | 27 | 72 | 1 |
Dec 23 | MTD Dec 23 | 28 | 31 | 22 | 59 | 13 | 9 | 20 | 7 | 26 | 63 | 93 | 31 |
Dec 23 | YTD 2023 | 350 | 309 | 309 | 692 | 194 | 149 | 171 | 199 | 216 | 553 | 574 | 649 |
What does your raw data look like?
Need more details to understand it.
Hi, I just updated my post. It now includes the dummy data that I used.
User | Count |
---|---|
107 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |