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 Power Bier's,
I am half way into the saga of finding a value for the difference in cost of a unique part number on my direct query table.
The value are:
The goal is to calculate the difference of the last entry by date minus the second last entry by date for a unique part number.
As an Example:
Part Number A had a cost of $11 dollars on 6/5/2018 and the last time we made them it was 4/4/2018 at a cost of $14.
So Idealy the first row shows a column with Cost Diff = $-3 (part was cheaper to make).
That would repeat for all rows comparing the current date with previous date.
The equation to find the second last date (prior date) is the following:
Prior Date = CALCULATE(MAX(Finish[Date]), FILTER(Finish, Finish[Date] < MAX(Finish[Date])))
But I am stuck bulding a table like the one above
Solved! Go to Solution.
try this one
Measure =
VAR MxDt =
CALCULATE (
MAX ( Data[Date] ),
ALLEXCEPT ( Data, Data[PartNmber] ),
Data[Date] < CALCULATETABLE ( VALUES ( Data[Date] ) )
)
RETURN
VAR PrevCost =
CALCULATE (
VALUES ( Data[Cost] ),
ALLEXCEPT ( Data, Data[PartNmber] ),
Data[Date] = MxDt
)
RETURN
IF (
ISBLANK ( PrevCost ),
BLANK (),
CALCULATETABLE ( VALUES ( Data[Cost] ) ) - PrevCost
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Are you looking to build a calculated column?
This DAX may help you:
Column =
VAR PrevDate =
CALCULATE (
MAX ( Finish[Date] ),
ALLEXCEPT ( Finish, Finish[PartNumber] ),
Finish[Date] < EARLIER ( Finish[Date] )
)
VAR PrevCost =
CALCULATE (
VALUES ( Finish[Cost] ),
ALLEXCEPT ( Finish, Finish[PartNumber] ),
Finish[Date] = PrevDate
)
RETURN
IF ( ISBLANK ( PrevCost ), BLANK (), Finish[Cost] - PrevCost )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thank you very much. This solution worked for the example.
I think a calculated column is what I need. However, I could only use a new measure column on my actual table.
It did not work for the actual table I am working on.
Dax Error was: "EARLIER/EARLIEST refers to an earlier row context wich doesn't exist"
Looks like I won't be able to make a calculated column on a direct query.
Unfortunately the measured column is the only one that will work.
try this one
Measure =
VAR MxDt =
CALCULATE (
MAX ( Data[Date] ),
ALLEXCEPT ( Data, Data[PartNmber] ),
Data[Date] < CALCULATETABLE ( VALUES ( Data[Date] ) )
)
RETURN
VAR PrevCost =
CALCULATE (
VALUES ( Data[Cost] ),
ALLEXCEPT ( Data, Data[PartNmber] ),
Data[Date] = MxDt
)
RETURN
IF (
ISBLANK ( PrevCost ),
BLANK (),
CALCULATETABLE ( VALUES ( Data[Cost] ) ) - PrevCost
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
That worked out really well!
The measure column makes the calculation very slow, but that is okay.
Thanks for the help!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |