Forum Discussion

bramos88's avatar
bramos88
Frequent Visitor
8 years ago
Solved

Calculating two values with one month difference

Hello everyone!   I'm having some trouble with calculating the division of two sums with one month difference between them.   Here's what I want to do: I have two columns, A and B. MEASURE = SUM...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    This data is not well structured.  Using the Query Editor, we should right click on the Date column and the right column and then unpivot the other columns to convert your dataset into a 4 column one.  Once that is done, one can easily use the Date/Time Intelligence functions to get the growth over the previous month/year by writing simple measures.

bramos88's avatar
bramos88
Frequent Visitor
8 years ago

That's the idea of the calculation:

 


  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi bramos88,

     

    Please new a calculated column with below DAX:

    SumB/SumA = 
IF (
    'Table 1'[DATE].[MonthNo] < 12,
    CALCULATE (
        SUM ( 'Table 1'[B] ),
        FILTER (
            ALLEXCEPT ( 'Table 1', 'Table 1'[DATE].[Year], 'Table 1'[DATE].[Month] ),
            'Table 1'[DATE].[MonthNo]
                = EARLIER ( 'Table 1'[DATE].[MonthNo] ) + 1
                && 'Table 1'[DATE].[Year] = EARLIER ( 'Table 1'[DATE].[Year] )
        )
    ),
    CALCULATE (
        SUM ( 'Table 1'[B] ),
        FILTER (
            ALLEXCEPT ( 'Table 1', 'Table 1'[DATE].[Year], 'Table 1'[DATE].[Month] ),
            'Table 1'[DATE].[MonthNo]
                = 1 + 1
                && 'Table 1'[DATE].[Year]
                    = EARLIER ( 'Table 1'[DATE].[Year] ) + 1
        )
    )
)
    / CALCULATE (
        SUM ( 'Table 1'[A] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[DATE].[Year], 'Table 1'[DATE].[Month] )
    )

     

    Best regards,

    Yuliana Gu

    • bramos88's avatar
      bramos88
      Frequent Visitor
      8 years ago

      Hello v-yulgu-msft

       

      I tried to use that solution but I'm getting some trouble with these parts:

      IF (
    'Table 1'[DATE].[MonthNo] < 12,
      Consulta1[DATA_SALDO].[MonthNo] = EARLIER ( Consulta1[DATA_SALDO].[MonthNo] ) + 1

      I'm getting the error that is not possible to determine one single value for "year".

       

      Do you know something about it?

       

      Thanks!

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    This data is not well structured.  Using the Query Editor, we should right click on the Date column and the right column and then unpivot the other columns to convert your dataset into a 4 column one.  Once that is done, one can easily use the Date/Time Intelligence functions to get the growth over the previous month/year by writing simple measures.