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(E...
"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.

Ajendra's avatar
Ajendra
Resolver I
3 years ago

Hey,

 

Please try this one!!

 

  • 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")
          )