Forum Discussion
Calculate difference between different value in same column against its ID.
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
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
tharunkumarRTK Thank you very much,
its showing proper result , can you change the formaula Prev MOnth - Current Month on this Code.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