cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sarfkermali Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculating Month to Month Change.

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

捕获.PNG 

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

 

Regards,

Jimmy Tao

4 REPLIES 4
sarfkermali Frequent Visitor
Frequent Visitor

Month to Month Change Error

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.

Community Support Team
Community Support Team

Re: Calculating Month to Month Change.

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

sarfkermali Frequent Visitor
Frequent Visitor

Re: Calculating Month to Month Change.

Returns Infinity.

Community Support Team
Community Support Team

Re: Calculating Month to Month Change.

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

捕获.PNG 

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

 

Regards,

Jimmy Tao