Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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 di...
  • tharunkumarRTK's avatar
    tharunkumarRTK
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
tharunkumarRTK
Super User
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

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

    • tharunkumarRTK's avatar
      tharunkumarRTK
      Super User
      2 years ago

      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