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(B) / SUM(A) where the month of A is always a month before B

 

Example:

 

MEASURE = SUM(B - BEING MONTH = FEBRUARY) / SUM(A - BEING MONTH = JANUARY)

 

Can someone help me with that?

Thanks! 

  • 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.

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    If columns A and B contain Months or even dates, then how can you add them?  Share some data and show the expected result.

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

    Hi bramos88,

     

    Please provide sample data to illustrate your desired output. If possible, post an image which shows your expected result.

     

    Regards,

    Yuliana Gu

    • 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.