Search code examples
powerbidaxmeasure

Understanding Calculate Measure with variables

I have calculated the next measure that provides the TOTALYTD in the previous year:

enter image description here

It is working as expected. For dates before 01/07/2006, I do not have any value in my second table. However, if I change the formula using a variable, I'm getting this result:

enter image description here

The calculation is performed in the same year (2006). I have been trying to understand the context (kind of new with Dax), but I have not understood this result yet.

Solution

  • When you store a calculation in a variable, it becomes a constant, and can't be re-calculated. That's why DATEDD in your second measure has no effect.

    As a good practice, break your calculations into multiple measures. It allows you to re-use them, and makes your code more readable and maintainable. For example:

    Sales Amount = SUM(InternetSales[SalesAmount])

Sales YTD = TOTALYTD( [Sales Amount], Calendario[Date])

Sales YTD LY = CALCULATE( [Sales YTD], PREVIOUSYEAR( Calendario[Date]))