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] )
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Natty004 
First create the year month sequential number number (Calculated Column)

YearMonth Sequential Number =
RANKX (
    'Date',
    YEAR ( 'Date'[Date] ) * 100
        + MONTH ( 'Date'[Date] ),
    ,
    ASC,
    DENSE
)

Then the required measure would be

$ MArgine Increase =
VAR CurrentMonth =
    MAX ( 'Date'[YearMonth Sequential Number] )
VAR CurrentMonthRevenue = [TOTAL Revenue]
VAR CurrentMonthMarginPercent = [Margin %]
VAR PreviousMonthMarginPercent =
    CALCULATE ( [Margin %], 'Date'[YearMonth Sequential Number] = CurrentMonth - 1 )
RETURN
    CurrentMonthRevenue * ( CurrentMonthMarginPercent - PreviousMonthMarginPercent )
Natty004's avatar
Natty004
Frequent Visitor
4 years ago

Hello tamerj1

Thank you for your response, I have updated the following however I the $ increase value is the same as the $ margin value. 

 

Here are my updates:

 

 

 

YearMonth Sequential Number = 
RANKX (
    'Table1',
    YEAR ('Table1'[Dt Invoice]) * 100
        + MONTH ( 'Table1'[Dt Invoice] ),
    ,
    ASC,
    DENSE
)

 

 

 

Measure

 

$ MArgine Increase = 
VAR CurrentMonth =
    MAX ( Table1[YearMonth Sequential Number] )
VAR CurrentMonthRevenue = 'Table1'[Measure : TOTAL Revenue]
VAR CurrentMonthMarginPercent = [Measure : Margin %]
VAR PreviousMonthMarginPercent =
CALCULATE([Measure : Margin %],Table1[YearMonth Sequential Number]=CurrentMonth-1)
RETURN
    CurrentMonthRevenue * ( CurrentMonthMarginPercent - PreviousMonthMarginPercent )

 

The value that returns is the same as the margin $ amount

 

 

Measure : Total Margin = Table1[Measure : TOTAL Revenue]-Table1[Measure : Total Oncosts]

 

 

I have tried to calculate the current Margin % and the pror month margin %, however the results when I am pulling in the Prior month % is the same as current month

 

 

Current Month % = CALCULATE(
divide(Table1[Measure : Total Margin],
Table1[Measure : TOTAL Revenue],0), 
Table1[YearMonth Sequential Number])

 

 

 

 

Prior Month % = 
VAR CurrentMonthMargin = CALCULATE([Measure : Margin %], Table1[YearMonth Sequential Number])
VAR PriorMonthMargin = CALCULATE([Measure : Margin %], Table1[YearMonth Sequential Number] -1) 
RETURN
PriorMonthMargin

 

 

Then I tried to write the following however it did not return any results

 

 

$ margin increase = 
SUMX (
    Table1,
    [Measure : TOTAL Revenue]
        * (
            [Measure : Margin %]
                - ( CALCULATE ( [Measure : Margin %], Table1[YearMonth Sequential Number] - 1 ) )
        ))

 

 Apologies for the multiple screen shots, are you able to see where the fault is. I can't seem to increase the dollar amount that has been increased.

 

Any assitance would be greatly appreciated.

 

Thank you in advance,

 

Nat