Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Modifying quick measure YoY%

I tried to modify the quick measure (Year over year change). Original DAX looks like:

 

Taxi Out Time, min YoY% =

VAR __PREV_YEAR = CALCULATE(SUM('QAR data'[Taxi Out Time, min]), DATEADD('DATES'[DATE_FULL], -1, YEAR))

RETURN
DIVIDE(SUM('QAR data'[Taxi Out Time, min]) - __PREV_YEAR, __PREV_YEAR)

 

I decided to make some modification:

 

Taxi Out Time (sum), min YoY% =

VAR a = SUM('QAR data'[Taxi Out Time, min])
VAR prev_a = CALCULATE(a, SAMEPERIODLASTYEAR(DATES[DATE_FULL]))

RETURN
DIVIDE(a - prev_a, prev_a)

 

But new formula didn`t work well:

(the first column is a month name in Russian)

 

Please help to understand, what am I doing wrong?

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, Anonymous , the issue results from use of variable a in your measure; In fact, variables in DAX are NOT THAT VARIABLE as expected!

     

    In DAX, variables are calculated within the scope in which they are written, and then the result of them is stored and used in the rest of the expression.

     

    You might want to refer to an article on this subject for more details.

     

    A straightforward way is to create a measure a = SUM('QAR data'[Taxi Out Time, min]), and you can reference it in another measure.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Make share months are coming from Dates Table

     

    This year = SUM('QAR data'[Taxi Out Time, min])
    last Year = CALCULATE([This year], SAMEPERIODLASTYEAR(DATES[DATE_FULL]))

    Change % =
    DIVIDE([This year] - [last Year], [last Year])

     

    Please provide your feedback comments and advice for new videos
    Tutorial Series Dax Vs SQL Direct Query PBI Tips
    Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Variables in DAX are CONSTANTS. Once calculated, they are NEVER re-calculated.