Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sarfkermali
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

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

View solution in original post

4 REPLIES 4
sarfkermali
Frequent Visitor

Returns Infinity.

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

v-yuta-msft
Community Support
Community Support

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.