Different sum based on type column

[giuliapiazza94]

Hi guys,

I've a table like this

This table is linked to calendar table.

In report I use a filter to set a time period, for example: from 19/03/2024 to 30/04/2024.

I need to create a matrix:

Type    amount

A         ..........

B         ...........

My measure must calculate the sum of type A from beginning of the year to until the day before the period I selected (18/03/2024) and the sum of type B from 19/03/2024 to 30/04/2024.

filter date:  from 19/03/2024 to 30/04/2024

For example:

Type     amount

A             600+120+23+77=820

B             1+72=72

Thank you all 🙂

[Anonymous · Accepted solution]

Thanks for the reply from @tamerj1 , please allow me to provide another insight: 

Hi  @giuliapiazza94 ,

Here are the steps you can follow：

1. Create calculated table – slicer table.

Date =
CALENDAR(
    DATE(2024,1,1),DATE(2024,4,30))

2. Create measure.

Measure =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
var _perioddate=_mindate-1
var _until=
DATE(YEAR(_mindate),1,1)
return
IF(
    MAX('Table'[Type])="A",
    SUMX(
        FILTER('Table',
      'Table'[Date]>=_until&&'Table'[Date]<=_perioddate),[Amount]),
    SUMX(
        FILTER('Table',
       'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate),[Amount]))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tamerj1]

Hi @giuliapiazza94 
Please try

AmountMeasure =
SUMX (
    VALUES ( 'Table'[Type] ),
    IF (
        'Table'[Type] = "A",
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            'Calendar'[Date] < MIN ( 'Calendar'[Date] )
        ),
        SUM ( 'Calendar'[Amount] )
    )
)