Percentage Cost Change Month over Month Per Item Code

Hello,

I am trying to create a formula to identify the % change month over month for each item code for the last 2 years. And then set a threshold so anything that has not had a change of more than x% is highlighted, so I can do more analysis on. I have a dollar value column and a quantity column. So, I created a per item code cost calculation and below is the formula I used.

Item Cost = DIVIDE(SUMX(Table, Table[$ USD]), SUMX(Table, Table[Qty]))

However, I am stuck on what to do next. I have tried multiple formulas, but none seem to be giving me the correct result. I created a date table and joined it to my fact table. T

his is an example of the data:

Date Item Code $ USD Qty

1/1/2012 123456789 174.1460147 4000

1/1/2012 234567891 610.8506363 24000

1/1/2012 345678912 815.8070998 58000

v-juanli-msft

7 years ago

Hi cayonice

[Date] is in fact table, not refer to the "date" in date table.

This formula is a measure not a column.

Item Cost-LM =
CALCULATE (
    SUM ( 'Table'[$ USD] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Item Code ] ),
        YEAR ( 'Table'[Date ] ) = MAX ( Dates[Year] )
            && MONTH ( 'Table'[Date ] )
                = MAX ( Dates[Month Number] ) - 1
    )
)
    / CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Item Code ] ),
            YEAR ( 'Table'[Date ] ) = MAX ( Dates[Year] )
                && MONTH ( 'Table'[Date ] )
                    = MAX ( Dates[Month Number] ) - 1
        )
    )

Update my pbix

Best Regards

Maggie

Percentage Cost Change_v2.pbix68 KB

10 Replies

Anonymous
Not applicable
7 years ago
Do you have a dedicated calendar table? Can leverage the built-in time intelligence functions but need a dedicated calendar table.
- cayonice
  Frequent Visitor
  7 years ago
  Hi Nick,
  
  I do have a dedicated calendar table created.
  
  I used this to create a calendar table:
  Dates =
  VAR BaseCalendar = CALENDARAUTO(6)
  RETURN GENERATE(BaseCalendar,
  VAR BaseDate = [Date]
  VAR YearDate = YEAR(BaseDate)
  VAR MonthNumber = MONTH(BaseDate)
  RETURN ROW("Day", BaseDate, "Year", YearDate, "Month Number", MonthNumber, "Month", FORMAT(BaseDate, "mmm"), "Year Month", FORMAT(BaseDate, "mmm yyyy")
  ))
v-juanli-msft
Community Support
7 years ago
Hi cayonice

If you'd like to compare cost of this month(ect. month2) and last month(ect.month1), create measures as below

Item Cost-TM = DIVIDE(SUMX('Table','Table'[$ USD]),SUMX('Table','Table'[Qty])) Item Cost-LM =
CALCULATE (
SUM ( 'Table'[$ USD] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Item Code ] ),
YEAR ( [Date ] ) = MAX ( Dates[Year] )
&& MONTH ( [Date ] )
= MAX ( Dates[Month Number] ) - 1
)
)
/ CALCULATE (
SUM ( 'Table'[Qty] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Item Code ] ),
YEAR ( [Date ] ) = MAX ( Dates[Year] )
&& MONTH ( [Date ] )
= MAX ( Dates[Month Number] ) - 1
)
)

compare with last month =
IF (
NOT ( ISBLANK ( [Item Cost-TM] ) ) && NOT ( ISBLANK ( [Item Cost-LM] ) ),
( [Item Cost-TM] - [Item Cost-LM] )
/ [Item Cost-LM]
)

Then do conditional formating for the table or matrix.

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting

Best Regards

Maggie
- cayonice
  Frequent Visitor
  7 years ago
  Thank you, Maggie! I will try this and let you know the outcome.
- cayonice
  Frequent Visitor
  7 years ago
  Hi! Unfortunately the formula is not working for me. I get mostly zeros or 0.01 for the prior month eventhough that is not the correct prior month price.
  What is the ( [Date ] ) referring to?
  
  Thanks so much!
  - v-juanli-msft
    Community Support
    7 years ago
    Hi cayonice
    
    [date] is a column from my Date table, this is a calendar date table which connects to the Table1.
    
    Please refer to my pbix for the two solutions above.
    
    Page1->solution1
    
    Page2->solution2
    
    Best Regards
    
    Maggie
    
    Percentage Cost Change.pbix64 KB
v-juanli-msft
Community Support
7 years ago
Hi cayonice

If you'd like to compare the cost of this month this year with the cost of the same month of the last year,

For example, select a year "2012"from the slicer, you would get the percent

year month cost change%

2011 1 1

2012 1 2 (2-1)/1

2011 2 2

2012 2 4 (4-2)/2

If so, cretae measures as below

Item Cost-TM = DIVIDE(SUMX('Table','Table'[$ USD]),SUMX('Table','Table'[Qty])) Item Cost-LY = CALCULATE([Item Cost-TM],DATEADD(Dates[Date],-1,YEAR)) percentage = IF(NOT(ISBLANK([Item Cost-LY])), ([Item Cost-TM]-[Item Cost-LY])/[Item Cost-LY])

then add conditional formatting on the [percentage] measure

Best Regards

Maggie