CODEWITHSUNDEEP
powerbidax
Sam332
Sam332

Reputation: 67

DATEADD throwing an error when adding MAX

I am looking to get the sum of sales for the last 3 months. not including the unfinished month which is this month. I have this Dax but its not accepting the DATEADD with the MAX. Is there any workaround?

Measure1 = CALCULATE ( SUM ( table1[Sales] ), DATESINPERIOD ( table1[date], DATEADD( MAX ( table1[date] ), -1,MONTH), -3, MONTH ) )

Upvotes: 0

Views: 1157

Answers (1)

intruderr
intruderr

Reputation: 353

DATEADD function requires a set of dates to computation. That is why it not works with MAX, which returns single value. Find more here.

You need to find the maximum date in the dataset, then filter out the current month from the date table. Try the measure as follows:

Measure1 = 
VAR maxDate = 
    CALCULATE(
        MAX( 'Calendar'[Date] ),
        ALL('Calendar' )
    )
VAR firstDay = DATE( YEAR( maxDate ), MONTH( maxDate ), 1 ) 
VAR maxK = 
    CALCULATE(
        MAX('Calendar'[Date] ),
        'Calendar'[Date] < firstDay
    )
VAR result = 
    CALCULATE( 
        SUM( AmountPaid[PaymentAmt] ), 
        DATESINPERIOD('Calendar'[Date], maxK, -3, MONTH )
    )
return result

The maxK part calculates maximum date excluding the latest month in my dataset. You have to adjust the measure a bit for your needs (e.g. use TODAY() instead maxDate).

Hope it helps.

Upvotes: 1

Related Questions