Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
It worked, when I added measures to new table and made data link between original table and new one.
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.
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?
It worked, when I added measures to new table and made data link between original table and new one.
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".
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?
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
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
17 | |
11 | |
5 | |
4 | |
3 |