topic Calculate difference between different value in same column against its ID. in DAX Commands and Tips

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

Anonymous — Sun, 30 Jun 2024 11:38:43 GMT

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

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

tharunkumarRTK — Sun, 30 Jun 2024 13:48:50 GMT

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

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

lbendlin — Sun, 30 Jun 2024 13:49:18 GMT

Have you considered using Visual Calculations?

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

Anonymous — Mon, 01 Jul 2024 05:00:56 GMT

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 .

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

Anonymous — Mon, 01 Jul 2024 05:16:18 GMT

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

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

tharunkumarRTK — Mon, 01 Jul 2024 10:34:45 GMT

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

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

Anonymous — Mon, 01 Jul 2024 12:29:01 GMT

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

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

tharunkumarRTK — Mon, 01 Jul 2024 15:23:59 GMT

Anonymous

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

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

Anonymous — Tue, 02 Jul 2024 08:48:07 GMT

Thank you Bro,.