Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mrfsca
Frequent Visitor

Calculate Difference Value FromMultiple Selected Value

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?

mrfsca_0-1704955764737.png

(pic1)

mrfsca_1-1704955885337.png

(pic2)

Data that I used (dummy)

PeriodPeriod InformationADACDDTAUIDTSDTNDTNRLAPRROEOEE
Jan 21MTD Jan 21292924263130302528721076
Feb 21MTD Feb 21282225526280029705595
Mar 21MTD Mar 21302121192513126436885
Apr 21MTD Apr 2130292713253124613494734
May 21MTD May 21292524402917251311673153
Jun 21MTD Jun 2130313136351318219861
Jul 21MTD Jul 2128292994302921623675091
Aug 21MTD Aug 213029203227292620232919
Sep 21MTD Sep 21292727924252527721172
Oct 21MTD Oct 212826317314242218645337
Nov 21MTD Nov 2129232373303025123111933
Dec 21MTD Dec 2128302160902828661417
Dec 21YTD  2021348321303480273261221149181635407583
Jan 22MTD Jan 22303128680142208251859
Feb 22MTD Feb 22282928552216281111321176
Mar 22MTD Mar 2231252159131324133029236
Apr 22MTD Apr 223130272102624151673884
May 22MTD May 222922244634286336397
Jun 22MTD Jun 222820257910248267698813
Jul 22MTD Jul 22282523161071652868633
Aug 22MTD Aug 223125284624292292871702
Sep 22MTD Sep 22292822141221285206055
Oct 22MTD Oct 22302124402122611512831
Nov 22MTD Nov 22302925632414151321678433
Dec 22MTD Dec 2231252272306111013848260
Dec 22YTD  2022356310297579158197159159162590701469
Jan 23MTD Jan 232920218981310819471523
Feb 23MTD Feb 23292330302499223156665
Mar 23MTD Mar 2328272999212281812527484
Apr 23MTD Apr 232822257081722218891747
May 23MTD May 23292430970102121964417
Jun 23MTD Jun 23282530132513271824204713
Jul 23MTD Jul 233026204226626151344100
Aug 23MTD Aug 2330302873199132715459496
Sep 23MTD Sep 2330303022417241915613696
Oct 23MTD Oct 2331222362292462326167276
Nov 23MTD Nov 23302921361702422627721
Dec 23MTD Dec 232831225913920726639331
Dec 23YTD  2023350309309692194149171199216553574649
2 REPLIES 2
Habitat
Regular Visitor

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.