Forum Discussion
Calculate difference between different value in same column against its ID.
i need to calculate the difference between different months value budgets, and the result should be the difference of two month
against the same ID. like, e.g., ID PE-262. Now i need to find the difference between March 23 - April 23= Variance in Apr.23 . As its
Mar.23 -Apr.23 = Varaince should appear in april.23
Apr.23 -May.23 = Varaince should appear in May.23
May.23 -June.23 = Varaince should appear in June.23
As i need variance in latest month, and the first month should appear 0, so used this code below , which is showing results, except when the year Change from Dec.23 to Jan.24 = varaince in Jan.24 , is showing the budget itself which is not right. need modfication for this step in this code , as other all results are accuartae , except then the year change from 2023 to 2024 only that specfic month is not ok. https://alfanargroup-my.sharepoint.com/:u:/g/personal/bilal_nawaz_alfanar_com/Ea7lulNnLsZNufAf3JH5QH0B8tyezDsHKX1iDqr_YcWUZw?e=AlJIjN
Differnce_month =
var _nextvalue=
SUMX(
FILTER(ALL('Table'),
'Table'[ID]=EARLIER('Table'[ID])&&YEAR('Table'[Version])=YEAR(EARLIER('Table'[Version]))&&
MONTH('Table'[Version])=MONTH(EARLIER('Table'[Version]))-1
),[Budget])
var _diff=
[Budget] - _nextvalue
var _minmonth=
MINX(ALL('Table'),[Version])
return
IF(
'Table'[Version]=_minmonth ,0,_diff)
| Description | ID | Version | Budget |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-Mar-23 | 5.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-Mar-23 | 2.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-Mar-23 | 3.00 |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-Apr-23 | 15.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-Apr-23 | 20.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-Apr-23 | 14.00 |
| Salarie.Project Office Team | PE-262-01-001-01 | 1-Apr-23 | 17.00 |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-May-24 | 12.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-May-24 | 13.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-May-24 | 9.00 |
| Salarie.Project Office Team | PE-262 | 1-Jun-24 | 8.00 |
| Salaries Site Office Team | PE-262-01 | 1-Jun-24 | 4.00 |
| Salarie.Site Support Team | PE-262-01-001 | 1-Jun-24 | 3.00 |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-Jul-23 | 5.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-Jul-23 | 2.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-Jul-23 | 3.00 |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-Aug-23 | 15.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-Aug-23 | 20.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-Aug-23 | 14.00 |
| Salarie.Project Office Team | PE-262-01-001-01 | 1-Aug-23 | 17.00 |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-Sep-24 | 12.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-Sep-24 | 13.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-Sep-24 | 9.00 |
| Salarie.Project Office Team | PE-262 | 1-Oct-24 | 8.00 |
| Salaries Site Office Team | PE-262-01 | 1-Oct-24 | 4.00 |
| Salarie.Site Support Team | PE-262-01-001 | 1-Oct-24 | 3.00 |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-Nov-23 | 5.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-Nov-23 | 2.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-Nov-23 | 3.00 |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-Dec-23 | 15.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-Dec-23 | 20.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-Dec-23 | 14.00 |
| Salarie.Project Office Team | PE-262-01-001-01 | 1-Dec-23 | 17.00 |
| Reinforcement of Thuwal Town KAUST 2 | PE-262 | 1-Jan-24 | 12.00 |
| SITE MANAGEMENT\OVERHEAD COST | PE-262-01 | 1-Jan-24 | 13.00 |
| SM1|SITE MANAGEMENT EXP - ADMINISTRATI | PE-262-01-001 | 1-Jan-24 | 9.00 |
| Salarie.Project Office Team | PE-262 | 1-Feb-24 | 8.00 |
| Salaries Site Office Team | PE-262-01 | 1-Feb-24 | 4.00 |
| Salarie.Site Support Team | PE-262-01-001 | 1-Feb-24 | 3.00 |
Anonymous
Differnce_month = Var __Project = 'Table'[ID] Var __FilterforCurrentProject = FILTER('Table', 'Table'[ID] = __Project) Var __FirstDateOfProject = MINX(__FilterforCurrentProject, 'Table'[Version] ) Var __PreviousMonth = EOMONTH('Table'[Version],-2) + 1 Var __PrevMonthBudget = MINX(FILTER(__FilterforCurrentProject, 'Table'[Version] = __PreviousMonth), 'Table'[Budget]) RETURN IF('Table'[Version] <> __FirstDateOfProject, __PrevMonthBudget - 'Table'[Budget] , 0)Need Power BI consultation, hire me on UpWork .
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
8 Replies
Have you considered using Visual Calculations?
Anonymous
There two mistakes in your formula. In _minMonth variable, you are taking least date value in version column. Which is march 1st 2023. In the return statement you are checking, if current value of version is minMonth (march 1st 2023) if yes then then 0 else _diff. This is not correct.
As per your expectation, for 2023 your first month is march, as you do not have past records. For 2024 and also for future years your first month is January. So, you need to write the formula accordingly. There are many ways to do this, this is one:Differnce_month = var _nextvalue= SUMX( FILTER(ALL('Table'), 'Table'[ID]=EARLIER('Table'[ID])&&YEAR('Table'[Version])=YEAR(EARLIER('Table'[Version]))&& MONTH('Table'[Version])=MONTH(EARLIER('Table'[Version]))-1 ),[Budget]) var _diff= [Budget] - _nextvalue var __FirstMonth = 1 var _minmonth= MIN('Table'[Version]) return IF( MONTH('Table'[Version]) = __FirstMonth || 'Table'[Version] = _minmonth ,0,_diff)I am not sure why you are doing this in a caclualted column, be aware calculated columns are not good for your semantic model.
Need Power BI consultation, hire me on UpWork .
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
tharunkumarRTK
Also if you see your code is not reflecting result in Sep.23 as there should be variance but its showing budget value,Anonymous
In your data sample, you dont have any project budgets for 2024 august, thats the reason for september month, it is showing the actual budget value.I have updated the formula and now you will see value for Jan 2024. (I assumed the formula as current month minus previous month, if not you can edit my code and use it)
Differnce_month = Var __Project = 'Table'[ID] Var __FilterforCurrentProject = FILTER('Table', 'Table'[ID] = __Project) Var __FirstDateOfProject = MINX(__FilterforCurrentProject, 'Table'[Version] ) Var __PreviousMonth = EOMONTH('Table'[Version],-2) + 1 Var __PrevMonthBudget = MINX(FILTER(__FilterforCurrentProject, 'Table'[Version] = __PreviousMonth), 'Table'[Budget]) RETURN IF('Table'[Version] <> __FirstDateOfProject, 'Table'[Budget] - __PrevMonthBudget, 0)Need Power BI consultation, hire me on UpWork .
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
Dear tharunkumarRTK
as Per formula , it's showing in Jan.24 , value 0, which is not accurate as it should show variance of Dec.23 - Jan.24= Variance in Jan.24 if have , and same for Future years. only zero should reflect at the starting Month of the Project, as its, Mar.23 which is showing 0, accurate .
