Forum Discussion
Calculating % increase as dollar value - DAX
Community ChampionPlease try
Prior Month % =
CALCULATE (
[Measure : Margin %],
Table1[YearMonth Sequential Number]
= MAX ( Table1[YearMonth Sequential Number] ) - 1,
ALL ( Table1[Year & Month] )
)
Thanks tamerj1 for your response. Unfortunately didnt resolve the issue.
The issue I'm trying to resolve is to calculate the monetary amount to the margin increase applied.
Within Excel the formula is = CURRENT MONTH REVENUE * (CURRENT MONTH MARGIN % - Previous Month margin %).
Iām presenting the data in a MATRIX table ā so that the user can select the correct months.
List of measures:
TOTAL $ Revenue = sum('MASTER DATA'[Net])
Total $ Oncosts = sumx('MASTER DATA',('MASTER DATA'[cost 1]+'MASTER DATA'[cost 2]+'MASTER DATA'[Other 1]+'MASTER DATA'[Other 2]+'MASTER DATA'[cost 3]+'MASTER DATA'[cost 4])*'MASTER DATA'[Bill UTY])
Total $ Margin = [TOTAL $ Revenue]-[Total $ Oncosts]
Margin % = divide('PIP Measures'[Total $ Margin],'PIP Measures'[TOTAL $ Revenue],0)
I've added - Year Month sequential number
The measure below is returning the same amount as Total $ Margin - I cant seem to work out why its going wrong?š¤
Margin $ Increase - V2 =
VAR CurrentMonth =
MAX ( 'MASTER DATA'[YearMonth Sequential Number] )
VAR CurrentMonthRevenue = 'PIP Measures'[TOTAL $ Revenue]
VAR CurrentMonthMarginPercent = 'PIP Measures'[Margin %]
VAR PreviousMonthMarginPercent =
CALCULATE('PIP Measures'[Margin %], 'MASTER DATA'[YearMonth Sequential Number]=CurrentMonth-1)
RETURN
CurrentMonthRevenue * ( CurrentMonthMarginPercent - PreviousMonthMarginPercent )
I've mocked up some data below to demonstrate the formula in excel = CURRENT MONTH REVENUE * (CURRENT MONTH MARGIN % - Previous Month margin %).
| Jun-22 | Jul-22 | ||||||
| Name | Revenue | Margin | % | Revenue | Margin | % | $ margin Increase |
| dummy data 1 | $ 27,184.05 | $ 2,104.77 | 7.74% | $ 18,246.45 | $ 2,386.43 | 13.08% | $ 973.67 |
| dummy data 2 | $ 13,985.41 | $ 1,991.56 | 14.24% | $ 6,516.47 | $ 1,134.41 | 17.41% | $ 206.45 |
| dummy data 3 | $ 5,961.69 | $ 615.98 | 10.33% | $ 8,049.43 | $ 962.70 | 11.96% | $ 131.01 |
If its easy to identify where I am going wrong - would be much appreciated š