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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ysapiyev
Responsive Resident
Responsive Resident

How to insert difference between two values in visual

Hi everyone,

 

I am building visual, which will display difference between two latest values. I have set of data, according to dates. I created measure, which calculates difference between two latest values: 

CALCULATE (
    SUM ( 'VT_WELL_TEST_en_US'[value1] );
    FILTER (
        'Table1';
        'Table1'[date]
            = CALCULATE (
                MAX ( 'Table1'[date] );
                FILTER ( Table1; 'Table1'[item_name] = EARLIER ( 'Table1'[item_name] ) )
            )  
    ) 
) - CALCULATE ( SUM ( 'Table1'[value1] );
    FILTER (
        'Table1';
        'Table1'[date]
            = CALCULATE (
                MAX ( 'Table1'[date] );
                FILTER ( Table1; 'Table1'[date]<>MAX('Table1'[date])                )
            )
    )
)

However, when I insert this measure in visual, it displays me just values for latest date, not difference. What can be one, to display difference?

Also, I group values in visual, since they have to differ according to date.

1 ACCEPTED SOLUTION
ysapiyev
Responsive Resident
Responsive Resident

It worked, when I added measures to new table and made data link between original table and new one.

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

Tough to say exactly what is going on here. What I would do would be to break this into 3 measures. like this:

 

Measure1 = CALCULATE (
    SUM ( 'VT_WELL_TEST_en_US'[value1] );
    FILTER (
        'Table1';
        'Table1'[date]
            = CALCULATE (
                MAX ( 'Table1'[date] );
                FILTER ( Table1; 'Table1'[item_name] = EARLIER ( 'Table1'[item_name] ) )
            )  
    ) 
)

Measure2 = CALCULATE ( SUM ( 'Table1'[value1] );
    FILTER (
        'Table1';
        'Table1'[date]
            = CALCULATE (
                MAX ( 'Table1'[date] );
                FILTER ( Table1; 'Table1'[date]<>MAX('Table1'[date])                )
            )
    )
)

Measure3 = Measure1 - Measure2

Should be easier to troubleshoot that way and ensure that your values are coming back correctly.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler,

 

Hi again. 

 

I got question regarding measures. When I calculate measure 3, as it was shown above, and when I insert it to table visual with item name, date, measure 3 demonstrates same values as value1, when it should demonstrate difference. So, how to alter DAX code to have dependence from date and name?

ysapiyev
Responsive Resident
Responsive Resident

It worked, when I added measures to new table and made data link between original table and new one.

@Greg_Deckler,

 

Can I create another table which will contain item name, latest date for this item and difference between values using DAX code?

Sure, you would typically use SUMMARIZE or ADDCOLUMNS. You can do this within a measure or in Modeling tab click "New Table".


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler

 

I have an issue with summarize function. I couldn't add measure 3 in new table. When I use same function to create columninstead of measure, it gives me different results. What can be wrong?

 

@Greg_Deckler,

 

I tried this approach. However, it transfers not difference, but latest value. I guess it happens because inputs for my visual grouped by date. 

 

To clarify my structure. I have 4 inputs, one of them should be difference between two values. These inputs are grouped by date and item name. And when I group it by date, measure3, which should give me difference, gives me latest value, instead of difference

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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