Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

dax expressions

Is there a cleaner way to write this expressions?

 

YTD_Variance_Matrix = Calculate(TOTALYTD([MTD_Actuals_Matrix],V_FS_DIM_DATE[act_date])-TOTALYTD([MTD_Budget_Matrix],V_FS_DIM_DATE[act_date]),KPI_Finance_Matrix[Group]="Revenue")&
(Calculate(TOTALYTD([MTD_Budget_Matrix],V_FS_DIM_DATE[act_date])-TOTALYTD([MTD_Actuals_Matrix],V_FS_DIM_DATE[act_date]),KPI_Finance_Matrix[Group]="Operating Expenses"))

 

THe output is coming out as a text value, need to convert to a numeric, for formatting purposes

  • Anonymous's avatar
    Anonymous
    7 years ago

    First of all, I put your code in www.daxformatter.com to come up with the following:

     

    YTD_Variance_Matrix =
    CALCULATE (
        TOTALYTD (
            [MTD_Actuals_Matrix],
            V_FS_DIM_DATE[act_date]
        ) - TOTALYTD (
                [MTD_Budget_Matrix],
                V_FS_DIM_DATE[act_date]
            ),
        KPI_Finance_Matrix[Group] = "Revenue"
    ) & (
            CALCULATE (
                TOTALYTD (
                    [MTD_Budget_Matrix],
                    V_FS_DIM_DATE[act_date]
                ) - TOTALYTD (
                        [MTD_Actuals_Matrix],
                        V_FS_DIM_DATE[act_date]
                    ),
                KPI_Finance_Matrix[Group] = "Operating Expenses"
            )
        )

    You can see about halfway down you use the character "&".

     

    This will concatenate the 2 otherwise numeric values into a single text string, probably not what you want.

     

    Consider replacing that & with a + or a - depending on the arithmetic you need to perform.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    First of all, I put your code in www.daxformatter.com to come up with the following:

     

    YTD_Variance_Matrix =
    CALCULATE (
        TOTALYTD (
            [MTD_Actuals_Matrix],
            V_FS_DIM_DATE[act_date]
        ) - TOTALYTD (
                [MTD_Budget_Matrix],
                V_FS_DIM_DATE[act_date]
            ),
        KPI_Finance_Matrix[Group] = "Revenue"
    ) & (
            CALCULATE (
                TOTALYTD (
                    [MTD_Budget_Matrix],
                    V_FS_DIM_DATE[act_date]
                ) - TOTALYTD (
                        [MTD_Actuals_Matrix],
                        V_FS_DIM_DATE[act_date]
                    ),
                KPI_Finance_Matrix[Group] = "Operating Expenses"
            )
        )

    You can see about halfway down you use the character "&".

     

    This will concatenate the 2 otherwise numeric values into a single text string, probably not what you want.

     

    Consider replacing that & with a + or a - depending on the arithmetic you need to perform.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank You CHris