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.
The dataset shows facility, material number, volume, volume change date, and the ranking (sorts the dates from earliest volume change date as 1 to the most recent). I would like to calculate the volume change between 2 dates for each material at each plant. See the table below of what I have and what I would like to achieve in Power BI.
What I have:
Facility Material Number Volume Volume Change Date Ranking
6879 333555 10,000.37 6/8/2006 1
6879 333555 125,433.00 1/28/2009 2
6879 333555 110,000.00 11/26/2019 3
6879 258852 3,052.00 5/12/2008 1
6879 258852 1,003.00 8/18/2016 2
3423 435303 21,000.00 3/23/2018 1
3423 435303 21,000.00 6/24/2019 2
3423 209890 - 10/4/2015 1
3423 209890 - 7/29/2020 2
What I would like to achieve:
Facility Material Number Volume Volume Change Date Ranking Volume Change
6879 333555 10,000.37 6/8/2006 1 0
6879 333555 125,433.00 1/28/2009 2 115,432.63
6879 333555 110,000.00 11/26/2019 3 (15,433.00)
6879 258852 3,052.00 5/12/2008 1 0
6879 258852 1,003.00 8/18/2016 2 (2,049.00)
3423 435303 21,000.00 3/23/2018 1 0
3423 435303 21,000.00 6/24/2019 2 0
3423 209890 - 10/4/2015 1 0
3423 209890 - 7/29/2020 2 0
I am still new to Power BI and DAX. Been boggling my mind on how to do this. Any help would be appreciated.
Solved! Go to Solution.
Hi @oldandnew
Based on your needs, I have created the following form
First you can create an index column in power query
Then you can use the following dax to achieve your goal
NewColumn =
IF (
'Table'[Ranking] = 1,
0,
'Table'[Volume] - LOOKUPVALUE('Table'[Volume], 'Table'[Index], 'Table'[Index] - 1)
)
This is the result
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @oldandnew
Based on your needs, I have created the following form
First you can create an index column in power query
Then you can use the following dax to achieve your goal
NewColumn =
IF (
'Table'[Ranking] = 1,
0,
'Table'[Volume] - LOOKUPVALUE('Table'[Volume], 'Table'[Index], 'Table'[Index] - 1)
)
This is the result
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This calculated column formula works
Column = if(ISBLANK(LOOKUPVALUE(Data[Volume],Data[Volume Change date],CALCULATE(MAX(Data[Volume Change date]),FILTER(Data,Data[Facility]=EARLIER(Data[Facility])&&Data[Material Number]=EARLIER(Data[Material Number])&&Data[Volume Change date]<EARLIER(Data[Volume Change date]))),Data[Facility],Data[Facility],Data[Material Number],Data[Material Number])),0,Data[Volume]-LOOKUPVALUE(Data[Volume],Data[Volume Change date],CALCULATE(MAX(Data[Volume Change date]),FILTER(Data,Data[Facility]=EARLIER(Data[Facility])&&Data[Material Number]=EARLIER(Data[Material Number])&&Data[Volume Change date]<EARLIER(Data[Volume Change date]))),Data[Facility],Data[Facility],Data[Material Number],Data[Material Number]))
Hope this helps.
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 |
---|---|
100 | |
100 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
106 | |
104 | |
85 | |
73 |