cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculating Month to Month Change.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculating Month to Month Change.

Hi

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```

Regards,

Jimmy Tao

4 REPLIES 4
Frequent Visitor

## Month to Month Change Error

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.

Community Support Team

## Re: Calculating Month to Month Change.

Hi

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

Frequent Visitor

## Re: Calculating Month to Month Change.

Returns Infinity.

Community Support Team

## Re: Calculating Month to Month Change.

Hi

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```

Regards,

Jimmy Tao