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 matr...
'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

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] )
    )
)