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 all,
i am working on a COVID-19 dashboard and i am facing a problem calculating the increase/decrease in the difference by percent.
look at the table below
Country/Region | Province/State | Date | Case_Type | Cases | Long | Lat | Difference | Last_Update_Date |
China | Anhui | 2020-01-22 | Deaths | 0 | 117.2264 | 31.8257 | 0 | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-23 | Deaths | 0 | 117.2264 | 31.8257 | 0 | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-24 | Deaths | 0 | 117.2264 | 31.8257 | 0 | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-22 | Confirmed | 1 | 117.2264 | 31.8257 | 1 | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-23 | Confirmed | 9 | 117.2264 | 31.8257 | 8 | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-24 | Confirmed | 15 | 117.2264 | 31.8257 | 6 | 2020-03-15 12:54:41 |
i want to calculate the increase and decrease in percentage day by day to look like this
Country/Region | Province/State | Date | Case_Type | Cases | Long | Lat | Difference | increase/decrease difference in percentage | Last_Update_Date |
China | Anhui | 2020-01-22 | Deaths | 0 | 117.2264 | 31.8257 | 1 | 0 | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-23 | Deaths | 0 | 117.2264 | 31.8257 | 2 | 100% | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-24 | Deaths | 0 | 117.2264 | 31.8257 | 8 | 300% | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-22 | Confirmed | 1 | 117.2264 | 31.8257 | 1 | 0 | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-23 | Confirmed | 9 | 117.2264 | 31.8257 | 8 | 700% | 2020-03-15 12:54:41 |
China | Anhui | 2020-01-24 | Confirmed | 15 | 117.2264 | 31.8257 | 6 | -25% | 2020-03-15 12:54:41 |
could you help me with that please?
Solved! Go to Solution.
Hi,
Please try this measure:
Measure =
VAR a =
IF (
MAX ( 'Table'[Difference] )
= MINX ( ALLEXCEPT ( 'Table', 'Table'[Case_Type] ), 'Table'[Difference] ),
1,
CALCULATE (
MAX ( 'Table'[Difference] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Case_Type] IN FILTERS ( 'Table'[Case_Type] )
&& 'Table'[Date] < MAX ( 'Table'[Date] )
)
)
)
RETURN
( MAX ( 'Table'[Difference] ) - a ) / a
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Hi,
Please try this measure:
Measure =
VAR a =
IF (
MAX ( 'Table'[Difference] )
= MINX ( ALLEXCEPT ( 'Table', 'Table'[Case_Type] ), 'Table'[Difference] ),
1,
CALCULATE (
MAX ( 'Table'[Difference] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Case_Type] IN FILTERS ( 'Table'[Case_Type] )
&& 'Table'[Date] < MAX ( 'Table'[Date] )
)
)
)
RETURN
( MAX ( 'Table'[Difference] ) - a ) / a
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
Hi, you should follow this post talking about the same and it has more activity:
I would recommend you to use PREVIOUSDAY dax function to solve the last day of each situation calculation as argument of filter in a CALCULATE.
Regards,
Happy to help!
Hi @mohd89ali ,
How are you making the calculation for increase/decrease not really sure how you are achiveing the values, made several try but cannot get the same values.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |