Forum Discussion

giuliapiazza94's avatar
giuliapiazza94
Icon for Helper IV rankHelper IV
2 years ago
Solved

Different sum based on type column

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 🙂

 

'Need help' 'DAX'
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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] )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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