Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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!
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
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
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
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
Thank you, Maggie! I will try this and let you know the outcome.
Do you have a dedicated calendar table? Can leverage the built-in time intelligence functions but need a dedicated calendar table.
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")
))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |