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,
Relatively new Power BI user here. I have one column that has account $ values and another column that has dates. When I bring the data into a matrix table I can filter the columns by particular dates and have rows by sales rep, but now I would like to calculate the difference in $ amounts and calculate the % difference between any 2 dates that I select. I know how to do this in Excel, but am completely unsure how to approach this in Power BI.
For example,
03/26/20 04/02/20 Difference % Difference
John $1,987,123 $2,345,961 ? ?
Steve $3,987,123 $5,345,961 ? ?
Thanks for your help!
Solved! Go to Solution.
Assumed you have joined with a date dimension and want to slicer min and max date. For static change the variable(Var) values
difference =
var _max = maxx('Date','Date'[Date])
var _min = Minx('Date','Date'[Date])
return
CALCULATE(SUM(Table[values ]), FILTER(all('Date'), 'Date'[Date] =_max)) -CALCULATE(SUM(Table[values ]), FILTER(all('Date'), 'Date'[Date] =_min))
difference =
var _max = maxx('Date','Date'[Date])
var _min = Minx('Date','Date'[Date])
return
divide(SUM(Table[values ]), FILTER(all('Date'), 'Date'[Date] =_max)) ,CALCULATE(SUM(Table[values ]), FILTER(all('Date'), 'Date'[Date] =_min))) -1
In case you need two slicer, refer to my blog :https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Assumed you have joined with a date dimension and want to slicer min and max date. For static change the variable(Var) values
difference =
var _max = maxx('Date','Date'[Date])
var _min = Minx('Date','Date'[Date])
return
CALCULATE(SUM(Table[values ]), FILTER(all('Date'), 'Date'[Date] =_max)) -CALCULATE(SUM(Table[values ]), FILTER(all('Date'), 'Date'[Date] =_min))
difference =
var _max = maxx('Date','Date'[Date])
var _min = Minx('Date','Date'[Date])
return
divide(SUM(Table[values ]), FILTER(all('Date'), 'Date'[Date] =_max)) ,CALCULATE(SUM(Table[values ]), FILTER(all('Date'), 'Date'[Date] =_min))) -1
In case you need two slicer, refer to my blog :https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi,
Please clarify/share the following:
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |