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

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Well, first, use Alt-Enter so that you can end up with:

     

    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 "&" is what is causing the return as text because that is the concatenation operator.

  • Anonymous's avatar
    Anonymous
    7 years ago

    I replaced the & with a + and it worked yeah!

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Well, first, use Alt-Enter so that you can end up with:

     

    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 "&" is what is causing the return as text because that is the concatenation operator.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      THank you very much, but Is there an alternative to using the & to join the calculate expressions together?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I replaced the & with a + and it worked yeah!