Forum Discussion

Natty004's avatar
Natty004
Frequent Visitor
4 years ago
Solved

Calculating % increase as dollar value - DAX

Hello,   I'm relatively new to powerBI and I'm trying to calculate the actual $ amount of margin increase compared to the margin % last month, and I'm struggling with the DAX (Therefore as we have ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Natty004 

    Please try

    Prior Month % =
CALCULATE (
    [Measure : Margin %],
    Table1[YearMonth Sequential Number]
        = MAX ( Table1[YearMonth Sequential Number] ) - 1,
    ALL ( Table1[Year & Month] )
)
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Natty004 , To calculate this you need add correct row context , assume these are measures

 

Sumx(Summarize(Fact, Fact[Name], Fact[Description], Date[Month Year], "_1" , [Current Revenue] * ([current month Margin %] - [Last months Margin %]) ) ), [_1])

 

Sumx(Summarize(Fact, Fact[Name], Fact[Description], Date[Month Year], "_1" , [Current Revenue] * ([current month Margin %] - [Last months Margin %]) ), [_1])

 

 

For this month last month you can use TI with date table

 

example

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

 

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Natty004's avatar
Natty004
Frequent Visitor
4 years ago

Hello amitchandak,

 

Thank you for your response, apologies I couldn't work out how to apply your example to a margin measure.

 

How do I calculate the current month and previous month margin %  when the margin measure is:

 

 Measure : Margin % = divide(Table1[Measure : Total Margin],Table1[Measure : TOTAL Revenue],0) 

 

 

These are my measures that I have added:

 

 MTD Margin % = calculate(DIVIDE(Table1[Measure : Total Margin], Table1[Measure : TOTAL Revenue]),DATESMTD(Dates[Date])) 

 

Last MTD margin =

CALCULATE (

DIVIDE ( Table1[Measure : Total Margin], Table1[Measure : TOTAL Revenue] ),

DATESMTD ( DATEADD ( Dates[Date], -1, MONTH ) )

) 

 

 

Previous month Margin % =

CALCULATE (

DIVIDE ( Table1[Measure : Total Margin], Table1[Measure : TOTAL Revenue] ), PREVIOUSMONTH(Dates[Date])) 

 

 

I'm not getting any results return from the above measures.

 

Apologies for the confusion, any assistance greatly appreciated.

 

many thanks

Nat