Get accurate matrix totals

[hprose]

Hello,

I have sample data in the below format. For simplicity's sake, I'm only including the customer dimension here, but my original data includes Manager, Director, etc. 

I want to create 2 matrixes from this data. One where the difference between MaxDate and MinDate is negative, one where it is positive. I have created such matrixes, but the row totals are incorrect.

Customer	Date	Amount
A	1/1/2022	5
A	1/2/2022	10
A	1/3/2022	10
A	1/4/2022	15
A	1/5/2022	20
A	1/6/2022	30
B	1/2/2022	40
B	1/3/2022	35
B	1/4/2022	40
B	1/5/2022	20
B	1/6/2022	15
B	1/7/2022	20
C	1/1/2022	15
C	1/2/2022	50
C	1/3/2022	100
C	1/4/2022	35
C	1/5/2022	100
D	1/3/2022	70
D	1/4/2022	50
D	1/5/2022	30
D	1/6/2022	20
D	1/7/2022	10
E	1/2/2022	100
E	1/3/2022	50
E	1/4/2022	70
E	1/5/2022	40

Amount change = 

var MinDateAmount = CALCULATE(SUM(Sheet1[Amount]),Sheet1[Date]=MIN(Sheet1[Date]))
var MaxDateAmount = CALCULATE(SUM(Sheet1[Amount]),Sheet1[Date]=MAX(Sheet1[Date]))

return
MaxDateAmount - MinDateAmount​

Using the above measure, I've created 2 matrixes. I've applied a visual level filter to each where Amount Change < 0, Amount Change > 0. However, the row totals are incorrect. I understand this is because the Min and Max amounts are being calculated at the overall level, resulting in this total value. How do I make the total add up to the column total? Thanks!

[hprose]

Will do @v-cgao-msft. Works great, thanks again!

[hprose]

Works perfectly! Thank you @v-cgao-msft 

[v-cgao-msft · Accepted solution]

Hi @hprose ,

Please try:

Amount change =
SUMX (
    SUMMARIZE (
        'Sheet1',
        'Sheet1'[Customer],
        "measure",
            CALCULATE ( SUM ( Sheet1[Amount] ), 'Sheet1'[Date] = MAX ( 'Sheet1'[Date] ) )
                - CALCULATE ( SUM ( Sheet1[Amount] ), 'Sheet1'[Date] = MIN ( 'Sheet1'[Date] ) )
    ),
    [measure]
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[hprose]

Hello @v-cgao-msft , apologies if this needs to go into a different thread. I was wondering how this measure could be modified if I wanted to get the amount difference between the first and last date, irrespective of whether the customer has data for those days.

For example, for A, the amount change should be -5, for B it should be 20, and so on. I was able to achieve this by hardcoding the dates in the Min and Max conditions. Can this is accomplished without hardocing the dates? Thanks again.

[v-cgao-msft]

Hi @hprose ,

Yes, please consider opening a new thread next time. Thanks.

Measure:

Measure = 
VAR _min_date = CALCULATE(MIN('Sheet1'[Date]),ALL())
VAR _max_date = CALCULATE(MAX('Sheet1'[Date]),ALL())
VAR _result = CALCULATE(SUM('Sheet1'[Amount]),'Sheet1'[Date]=_max_date)-CALCULATE(SUM(Sheet1[Amount]),'Sheet1'[Date]=_min_date)
RETURN
_result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data