Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
PBIX here: https://www.dropbox.com/s/t1zlz7ijxkwhem1/Calculating%20Month%20to%20Month%20Change..pbix?dl=0
Regards,
Jimmy Tao
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |