Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Good afternoon, the company where I work I need to make a report with the percentage variation in raw materials over the last three years. The downside is that raw materials don't go up every month so there are months when I don't have data.
Raw material value table
Columns: Year/month
Rows: Prices of different raw materials
The table shows that for example during October and November 2020 there was no price update.
For cases where there are ups in consecutive months, I had no problem getting the %
In other words, in this case I would be short to obtain the percentage change between September 2020 and December 2020 (among several other periods).
From now on I appreciate any contribution!!!
Best regards
Solved! Go to Solution.
HI @flitvak,
I think you may need to use Dax function to find out the last date that existed valid value in your table, then you can use this with the current category value to lookup corresponding value to compare with current and previous values.
measure =
VAR currDate =
MAX ( Table[Date] )
VAR prevDate =
CALCULATE (
MAX ( Table[Date] ),
FILTER ( ALLSELECTED ( Table ), [Date] < currDate && [Amount] <> BLANK () ),
VALUES ( Table[Category] )
)
VAR prevAmount =
CALCULATE (
SUM ( Table[Amount] ),
FILTER ( ALLSELECTED ( Table ), [Date] = prevDate ),
VALUES ( Table[Category] )
)
RETURN
DIVIDE ( SUM ( Table[Amount] ) - prevAmount, prevAmount )
Regards,
Xiaoxin Sheng
HI @flitvak,
I think you may need to use Dax function to find out the last date that existed valid value in your table, then you can use this with the current category value to lookup corresponding value to compare with current and previous values.
measure =
VAR currDate =
MAX ( Table[Date] )
VAR prevDate =
CALCULATE (
MAX ( Table[Date] ),
FILTER ( ALLSELECTED ( Table ), [Date] < currDate && [Amount] <> BLANK () ),
VALUES ( Table[Category] )
)
VAR prevAmount =
CALCULATE (
SUM ( Table[Amount] ),
FILTER ( ALLSELECTED ( Table ), [Date] = prevDate ),
VALUES ( Table[Category] )
)
RETURN
DIVIDE ( SUM ( Table[Amount] ) - prevAmount, prevAmount )
Regards,
Xiaoxin Sheng
Thank you very much Xiaoxin for the help!!! It was just what I needed!!!
As a whole I adapted the date in the currDate variable and it worked perfectly. The code looked like this to me:
Hi @flitvak,
I'm glad to hear my formula helps. 😊
If you have any other requirements, you can feel free to post here with detailed information.
Regards,
Xiaoxin Sheng
Have a separate table with all the months (preferably a proper calendar table) and then create a measure that calculates for each month what the latest price update is (either for the current month or for the max month that is smaller than the current month).
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
96 | |
90 | |
81 | |
69 |
User | Count |
---|---|
157 | |
125 | |
116 | |
111 | |
95 |