How to insert difference between two values in visual

[ysapiyev]

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.

[Greg_Deckler]

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.

[ysapiyev]

@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 · Accepted solution]

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

[ysapiyev]

@Greg_Deckler,

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

[Greg_Deckler]

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

[ysapiyev]

@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?

[ysapiyev]

@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