Calculate difference between different value in same column against its ID.

[Anonymous]

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/Ea7lulNnLsZNufAf3JH5QH... 

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]

Thank you Bro,.

[Anonymous]

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 .

[lbendlin]

Have you considered using Visual Calculations?

[tharunkumarRTK]

@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

[Anonymous]

@tharunkumarRTK 
Also if you see your code is not reflecting result in Sep.23 as there should be variance but its showing budget value,

[tharunkumarRTK]

@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

[Anonymous]

@tharunkumarRTK  Thank you very much, 
its showing proper result , can you change the formaula Prev MOnth - Current Month on this Code.

[tharunkumarRTK · Accepted solution]

@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