Percentage Cost Change Month over Month Per Item Code

[cayonice]

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]

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

[v-juanli-msft]

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]

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]

Hi @cayonice

Could you kindly confirm whether this problem sloved or not?

If it is sloved, could you kindly accept this thread as a solution so others may refer to?

If not, please ask me at your convenience.

Best Regards

Maggie

[cayonice]

Hi! It is still not working for me. My formula is not finding [Date]. This is why I asked where [Date] was coming from in your formula. I have a relationship built between the date table and the fact table, where the column I am joining to is set as a date format

[v-juanli-msft · Accepted solution]

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

[v-juanli-msft]

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

[cayonice]

Thank you, Maggie! I will try this and let you know the outcome. 

[Anonymous]

Do you have a dedicated calendar table?  Can leverage the built-in time intelligence functions but need a dedicated calendar table. 

[cayonice]

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")
))