Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate with and without variables difference

I have 2 calculate formulas: 

Formula 1: 

Emp Exits =
        CALCULATE(
            SUM(Emp_Exits[Employee Exits]),
            Emp_Exits[MonthNo]<=MAX(Emp_Exits[MonthNo]),
            ALL(Emp_Exits[Month Name])
            )
Formula 2:
Emp Exits with variable =
    VAR EmpExits = SUM(Emp_Exits[Employee Exits])
    RETURN
        CALCULATE(
            EmpExits,
            Emp_Exits[MonthNo]<=MAX(Emp_Exits[MonthNo]),
            ALL(Emp_Exits[Month Name])
            )
 
Both the formulas are same except that in Formula 2, i used variable.  See the below output screenshot.
Formula 1 is giving correct result and formula 2 is not giving the correct result. Why is this behaviour?
"CALCULATE"
variables
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    The measure

    Emp Exits with variable =
VAR EmpExits =
    SUM ( Emp_Exits[Employee Exits] )
RETURN
    CALCULATE (
        EmpExits,
        Emp_Exits[MonthNo] <= MAX ( Emp_Exits[MonthNo] ),
        ALL ( Emp_Exits[Month Name] )
    )

    is exactly the same as 

    Emp Exits with variable =
SUM ( Emp_Exits[Employee Exits] )

    The reason is that variables are only evaluated once therefore cannot be recalculated as measures inside a CALCULATE statement. Thus only the value of the first measure is always returned.

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      The MonthName column is derived from Month column using formula FORMAT([Month],"MMM").

      DATESYTD is working fine with Month but not working with MonthName column which is derived from Month column:

       
      Formula:
      CALCULATE(
              (1-DIVIDE(SUM(Emp_Exits[Employee Exits]),SUM(Emp_Exits[Approved Headcount]))),
              DATESYTD(Emp_Exits[Month])
              )
      • Ajendra's avatar
        Ajendra
        Resolver I
        3 years ago

        Try to create a seperate date table as shown below then apply your DAX accordingly.

         

        DateMaster =
            ADDCOLUMNS(CALENDAR(MIN(FactTable[Date]),MAX(FactTable[Date]))
            ,"Month",FORMAT([Date],"MMMM")
            ,"Sorting",FORMAT([Date],"YYYYMM")
            )
  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 
    The measure

    Emp Exits with variable =
VAR EmpExits =
    SUM ( Emp_Exits[Employee Exits] )
RETURN
    CALCULATE (
        EmpExits,
        Emp_Exits[MonthNo] <= MAX ( Emp_Exits[MonthNo] ),
        ALL ( Emp_Exits[Month Name] )
    )

    is exactly the same as 

    Emp Exits with variable =
SUM ( Emp_Exits[Employee Exits] )

    The reason is that variables are only evaluated once therefore cannot be recalculated as measures inside a CALCULATE statement. Thus only the value of the first measure is always returned.