Calculating Month to Month Change.

[sarfkermali]

Hi Everyone, i have a table as follows:

Location	Type	date	# people
calgary	Big	march, 2017	1143
toronto	Small	march, 2017	242
california	Small	march, 2017	609
mogadishu	Big	march, 2017	0
dar es salaam	Big	march, 2017	2238
calgary	Small	april, 2017	2668
toronto	Big	april, 2017	2020
california	Big	april, 2017	799
mogadishu	Small	april, 2017	1896
dar es salaam	Small	april, 2017	323
calgary	Big	may, 2017	165
toronto	Small	may, 2017	6518
california	Small	may, 2017	135
mogadishu	Big	may, 2017	516
dar es salaam	Big	may, 2017	16

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.

[sarfkermali]

Returns Infinity.

[v-yuta-msft · Accepted solution]

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

[v-yuta-msft]

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]

Hi Everyone, i have a table as follows:

Location	Type	date	# people
calgary	Big	march, 2017	1143
toronto	Small	march, 2017	242
california	Small	march, 2017	609
mogadishu	Big	march, 2017	0
dar es salaam	Big	march, 2017	2238
calgary	Small	april, 2017	2668
toronto	Big	april, 2017	2020
california	Big	april, 2017	799
mogadishu	Small	april, 2017	1896
dar es salaam	Small	april, 2017	323
calgary	Big	may, 2017	165
toronto	Small	may, 2017	6518
california	Small	may, 2017	135
mogadishu	Big	may, 2017	516
dar es salaam	Big	may, 2017	16

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.