Forum Discussion
Row Difference BY Period
Hi All,
I am calculating Earned Value by period based on the % of movement mulitplied by a budget figure.
On occassion the budget figure changes and therefore the total EV is not accurate due to the change in the budget at that given time.
What I would like to do is include a column that calculates the difference between the rows so I can mulitply this by the previous % value and then sum that with the total EV when applicable.
A sample of the data is posted below:
| Period | Previous % | Overall % | Budget | Difference |
| 30/09/2023 | 0 | 0 | 0 | 0 |
| 31/10/2023 | 0 | 20 | 401,932.00 | 401,932.00 |
| 30/11/2023 | 20 | 52 | 669,156.00 | 267,224.00 |
| 31/12/2023 | 52 | 74 | 669,156.00 | 0 |
Any help would be appreciated. Thank you.
Edit: Ashish_Mathur Greg_Deckler Thank you for your responses. I have updated the Difference column to reflect the expected results as requested.
Hi,
Assuming you have a Calendar Table with a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table AND to your visual, you have dragged Month and Year from the Calendar Table, write these measures:
Previous month budget = calculate([Budget],previousmonth(Calendar[date]))
Diff = [Budget]-[Previous month budget]
Hope this helps.
Hi Sime
If your source data table is like the sample data provided, you can create a calculated column with below DAX:
Difference = var curMonth = 'Table'[Period] var preBudget = CALCULATE(SUM('Table'[Budget]),ALL('Table'),'Table'[Period]=EOMONTH(curMonth,-1)) return 'Table'[Budget] - preBudgetBest Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
5 Replies
Sime See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousHi,
Based on the table that you have shared, show the expected result very clearly.
Hi Ashish, I have added the expected "Difference" values to the sample table data below and to the original thread:
Period Previous % Overall % Budget Difference 30/09/2023 0 0 0 0 31/10/2023 0 20 401,932.00 401,932.00 30/11/2023 20 52 669,156.00 267,224.00 31/12/2023 52 74 669,156.00 0 Any help would be appreciated. Thank you.
Hi,
Assuming you have a Calendar Table with a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table AND to your visual, you have dragged Month and Year from the Calendar Table, write these measures:
Previous month budget = calculate([Budget],previousmonth(Calendar[date]))
Diff = [Budget]-[Previous month budget]
Hope this helps.
Hi Sime
If your source data table is like the sample data provided, you can create a calculated column with below DAX:
Difference = var curMonth = 'Table'[Period] var preBudget = CALCULATE(SUM('Table'[Budget]),ALL('Table'),'Table'[Period]=EOMONTH(curMonth,-1)) return 'Table'[Budget] - preBudgetBest Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
