Forum Discussion
Difference between values in same column
Hi I hope you are Doing well I am trying to calculate the difference of followers like the expected difference any one who help me in this
thank you
| Month | Social Platform | Followers | Expected Difference |
| April | 303 | 0 | |
| May | 334 | 31 | |
| June | 347 | -13 | |
| July | 350 | -3 | |
| August | 366 | -16 | |
| September | 371 | -5 | |
| October | 374 | -3 | |
| November | 389 | -15 |
Hi mh2587 ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Create a calculated column to get the month number
Month Number = SWITCH('Table'[Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)2. Create a measure or calculated column to get the difference
Expected Difference = VAR _premonth = CALCULATE ( MAX ( 'Table'[Month Number] ), FILTER ( ALL ( 'Table' ), 'Table'[Month Number] < EARLIER ( 'Table'[Month Number] ) ) ) VAR _prefol = CALCULATE ( SUM ( 'Table'[Followers] ), FILTER ( ALL ( 'Table' ), 'Table'[Month Number] = _premonth ) ) RETURN IF ( ISBLANK ( _prefol ), 0, 'Table'[Followers] - _prefol )Best Regards
6 Replies
mh2587 ,
hope you have date else create date column like
date = datevalue("01-"&[Month] &"-2021")
then create a new column like
new column =
var _max = maxx(filter(Table, [Date] <earlier([Date])),[Followers])
return
[Followers] - maxx(filter(Table, [Date] = _max ),[Followers])In case you need measures , you can use time intelligence with date table
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKAIts not working giving the same value in the output
mh2587 , Sorry , My ba. In max you have to return date
new column =
var _max = maxx(filter(Table, [Date] <earlier([Date])),[Date])
return
[Followers] - maxx(filter(Table, [Date] = _max ),[Followers])
amitchandak AlexisOlson Can you guys help me in this will be appreciated
Hi mh2587 ,
I created a sample pbix file(see attachment), please check whether that is what you want.
1. Create a calculated column to get the month number
Month Number = SWITCH('Table'[Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)2. Create a measure or calculated column to get the difference
Expected Difference = VAR _premonth = CALCULATE ( MAX ( 'Table'[Month Number] ), FILTER ( ALL ( 'Table' ), 'Table'[Month Number] < EARLIER ( 'Table'[Month Number] ) ) ) VAR _prefol = CALCULATE ( SUM ( 'Table'[Followers] ), FILTER ( ALL ( 'Table' ), 'Table'[Month Number] = _premonth ) ) RETURN IF ( ISBLANK ( _prefol ), 0, 'Table'[Followers] - _prefol )Best Regards
