Forum Discussion

sarfkermali's avatar
sarfkermali
Frequent Visitor
8 years ago
Solved

Calculating Month to Month Change.

Hi Everyone, i have a table as follows:

 

LocationTypedate# people
calgaryBigmarch, 20171143
torontoSmallmarch, 2017242
californiaSmallmarch, 2017609
mogadishuBigmarch, 20170
dar es salaamBigmarch, 20172238
calgarySmallapril, 20172668
torontoBigapril, 20172020
californiaBigapril, 2017799
mogadishuSmallapril, 20171896
dar es salaamSmallapril, 2017323
calgaryBigmay, 2017165
torontoSmallmay, 20176518
californiaSmallmay, 2017135
mogadishuBigmay, 2017516
dar es salaamBigmay, 201716


I would like to calculate the % change of #people form month to month with regards to the type and location. 

 

Also, i tried some of the month to month solutions posted on the forum but i got an error in DAX: "A table of multiple values was supplied where a single value was expected".

 

I would really appreciate if you could use your expertise and show me how to do this.

  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi sarfkermali,

     

    Based on data you have provided, it's impossible to calculate change between current month and previous month. If you want to calculate change between current month and two month agos, modify DAX as below:

    Change By Month = 
VAR Previous_Date_Condition =  EOMONTH (MAX(Table1[date]), -3 ) + 1
VAR Current_Month =
    CALCULATE (
        SUM ( Table1[# people] ),
        ALLEXCEPT ( Table1, Table1[Location], Table1[Type] )
    )
VAR Previous_Month = 
    CALCULATE (
        SUM ( Table1[# people] ),
        FILTER(ALLEXCEPT ( Table1, Table1[Location], Table1[Type] ), Table1[date] = Previous_Date_Condition)
    )
RETURN
    ( Current_Month - Previous_Month )
        / Previous_Month

     

    PBIX here: https://www.dropbox.com/s/t1zlz7ijxkwhem1/Calculating%20Month%20to%20Month%20Change..pbix?dl=0 

     

    Regards,

    Jimmy Tao

4 Replies

  • sarfkermali's avatar
    sarfkermali
    Frequent Visitor
    8 years ago

    Hi Everyone, i have a table as follows:

     

    LocationTypedate# people
    calgaryBigmarch, 20171143
    torontoSmallmarch, 2017242
    californiaSmallmarch, 2017609
    mogadishuBigmarch, 20170
    dar es salaamBigmarch, 20172238
    calgarySmallapril, 20172668
    torontoBigapril, 20172020
    californiaBigapril, 2017799
    mogadishuSmallapril, 20171896
    dar es salaamSmallapril, 2017323
    calgaryBigmay, 2017165
    torontoSmallmay, 20176518
    californiaSmallmay, 2017135
    mogadishuBigmay, 2017516
    dar es salaamBigmay, 201716


    I would like to calculate the % change of #people form month to month with regards to the type and location. 

     

    Also, i tried some of the month to month solutions posted on the forum but i got an error in DAX: "A table of multiple values was supplied where a single value was expected".

     

    I would really appreciate if you could use your expertise and show me how to do this.

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi sarfkermali,

     

    Create a measure and try DAX below:

    Change By Month =
VAR Current_Month =
    CALCULATE (
        SUM ( Table1[# people] ),
        ALLEXCEPT ( Table1, Table1[Location], Table1[Type] )
    )
VAR Previous_Month =
    CALCULATE (
        SUM ( Table1[# people] ),
        ALLEXCEPT ( Table1, Table1[Location], Table1[Type] ),
        FILTER ( Table1, Table1[date] = EOMONTH ( Table1[date], -2 ) + 1 )
    )
RETURN
    ( Current_Month - Previous_Month )
        / Previous_Month

    Regards,

    Jimmy Tao

    • v-yuta-msft's avatar
      v-yuta-msft
      Community Support
      8 years ago

      Hi sarfkermali,

       

      Based on data you have provided, it's impossible to calculate change between current month and previous month. If you want to calculate change between current month and two month agos, modify DAX as below:

      Change By Month = 
VAR Previous_Date_Condition =  EOMONTH (MAX(Table1[date]), -3 ) + 1
VAR Current_Month =
    CALCULATE (
        SUM ( Table1[# people] ),
        ALLEXCEPT ( Table1, Table1[Location], Table1[Type] )
    )
VAR Previous_Month = 
    CALCULATE (
        SUM ( Table1[# people] ),
        FILTER(ALLEXCEPT ( Table1, Table1[Location], Table1[Type] ), Table1[date] = Previous_Date_Condition)
    )
RETURN
    ( Current_Month - Previous_Month )
        / Previous_Month

       

      PBIX here: https://www.dropbox.com/s/t1zlz7ijxkwhem1/Calculating%20Month%20to%20Month%20Change..pbix?dl=0 

       

      Regards,

      Jimmy Tao