CODEWITHSUNDEEP
excelpowerbipowerpivot
Boothy_79
Boothy_79

Reputation: 15

Passing a Variable in DAX

I'm trying to pass a variable through to a DATEADD function using PowerBI Desktop DAX so that it changes the Interval.

I'm using 

Sales Comparison = CALCULATE( [SALES], DATEADD('CALENDAR'[DATE],-1,YEAR))

to show sales in the same period last year, but i'd like the YEAR function to be variable.

The idea is to change it so the user can compare to last month, last quarter, etc.

I've created a Period table with month, quarter, year and I've tried to pass it through to show 

        Sales Comparison = 
        CALCULATE( [SALES],
 DATEADD('CALENDAR'[DATE],-1,VALUES(PERIOD[PERIOD])
        )
    )

It errors out showing "the Last Argument must be one of these keywords: DAY, MONTH, QUARTER or YEAR.

I've been replacing the VALUES with SELECTEDVALUE and VALUE; all of which fail on me.

Help on this would be greatly appreciated.

Upvotes: 1

Views: 1368

Answers (1)

Mike Honey
Mike Honey

Reputation: 15027

DAX can be a really frustrating language - so new and raw and yet so restrictive ...

I would duplicate the DATEADD 4 times (for DAY, MONTH, QUARTER and YEAR) and wrap the whole steaming mess in a SWITCH function. Something like:

Sales Comparison =
SWITCH (
    VALUES ( PERIOD[PERIOD] ),
    "DAY", CALCULATE ( [SALES], DATEADD ( 'CALENDAR'[DATE], -1, DAY ) ),
    "MONTH", CALCULATE ( [SALES], DATEADD ( 'CALENDAR'[DATE], -1, MONTH ) ),
    "QUARTER ", CALCULATE ( [SALES], DATEADD ( 'CALENDAR'[DATE], -1, QUARTER ) ),
    "YEAR", CALCULATE ( [SALES], DATEADD ( 'CALENDAR'[DATE], -1, YEAR ) )
)

Then I would need a shower.

Upvotes: 1

Related Questions