Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi y'all so I'm working on this report where for gas pipeline production daily. For some weird reason, they get teir daily data by subtracting the first value at opening on the previous day from the first value at opening on the current day. Each location has differnt opening times (7 am CT, Midnight CT and 1 am CT). My supervisor and I figured a calculated column would be the way to go rather than a measure. That way, we can take the current live value column and subract the previous day column from the current. It's been an headache for the last week now.
Here's the dax I currently have;
Prev. Value = IF('HEP PI'[KPI] = "Frontier" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-72),
IF('HEP PI'[KPI] = "Wahsatch" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-48),
IF('HEP PI'[KPI] = "UNEV" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Woods Cross Lines" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-72),
IF('HEP PI'[KPI] = "Mountain Home" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Osage" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-48),
IF('HEP PI'[KPI] = "Cushing Connect" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-96),
IF('HEP PI'[KPI] = "Tulsa 400" && 'HEP PI'[Hr] = 0, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Tulsa 500" && 'HEP PI'[Hr] = 0, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "12 in El Paso" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-48),
IF('HEP PI'[KPI] = "4C Art to Mor" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "4C Mor to Blf" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "White City to Artesia" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Artesia to Abo" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Abo to Centurion" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Artesia West (to Beeson)" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Artesia East (to Navajo)" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "LAC 16 in" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-48),
IF('HEP PI'[KPI] = "Beeson to Lovington" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-48),
IF('HEP PI'[KPI] = "Hobbs to Lovington" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Russell to Lovington" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-72),
IF('HEP PI'[KPI] = "Roadrunner" && 'HEP PI'[Hr] = 7, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-72),
IF('HEP PI'[KPI] = "10 in Empire" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "8 in Lovington" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "6 in El Paso" && 'HEP PI'[Hr] = 1, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Trust 6/8" && 'HEP PI'[hr] = 0, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-48),
IF('HEP PI'[KPI] = "Trust X6" && 'HEP PI'[Hr] = 0, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-48),
IF('HEP PI'[KPI] = "Fintex" && 'HEP PI'[Hr] = 0, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-24),
IF('HEP PI'[KPI] = "Dyess" && 'HEP PI'[Hr] = 0, LOOKUPVALUE('HEP PI'[Value],'HEP PI'[Index],'HEP PI'[Index]-48),0)))))))))))))))))))))))))))))
Currently, the dax isn't working as intended because I'm usiing the index column as a reference instead of date. The data isn't ordered correctly (date-wise) making the index column misordered. Can anyone help me get this right?
This can be simplified by using the following DAX formula:
Prev. Value =
VAR CurrIndex = 'HEP PI'[Index]
VAR PrevIndex = CurrIndex - SWITCH('HEP PI'[KPI],
"Frontier", 72,
"Wahsatch", 48,
"UNEV", 24,
"Woods Cross Lines", 72,
"Mountain Home", 24,
"Osage", 48,
"Cushing Connect", 96,
"Tulsa 400", 24,
"Tulsa 500", 24,
"12 in El Paso", 48,
"4C Art to Mor", 24,
"4C Mor to Blf", 24,
"White City to Artesia", 24,
"Artesia to Abo", 24,
"Abo to Centurion", 24,
"Artesia West (to Beeson)", 24,
"Artesia East (to Navajo)", 24,
24
)
RETURN LOOKUPVALUE('HEP PI'[Value], 'HEP PI'[Index], PrevIndex)
This formula uses a SWITCH statement to determine the number of hours to subtract from CurrIndex to get PrevIndex based on the value of 'HEP PI'[KPI]. If the KPI value is not found in the SWITCH statement, the default value of 24 is used. The result of PrevIndex is then used as the second argument in the LOOKUPVALUE function to return the previous day's value of 'HEP PI'[Value].
Thanks for simplifying my dax. I was a lot ti write lol. Is there a way to do the same thing but without using the index column? The dates are not ordered right so using the index to calculate the previous value has the tendency of returning a value from more that 2 days ago. The answer was consistent until it reached the 27th of January. Can we possibly use date instead without getting errors or wrong answers?
Please note that this formula assumes that your date column is sorted in ascending order. If your date column is not sorted, you may need to sort it first or modify the formula to match your data.
Let me know if this helps 🙂
Can I use my calendar table (connected to my main table) instead if my dates are not sorted right?
PrevValue = CALCULATE( SUM(Table1[Value]), FILTER( ALL(Table1), Table1[Date] = MAX(Table1[Date]) - 1 ) )
This formula uses the FILTER function to get the previous value based on the Date column. The ALL function is used to remove any existing filters on the date column, and the MAX function is used to get the latest date in the data set.
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
This dax gave me the sum rather than the values themselves.
Yes, you can use the date column instead of an index column to calculate the previous value. You can use the function LAG to get the previous value based on the date column.
It looks like the Prev. Value calculated column you have is meant to determine the value of the Value column from the previous day for each location. The number of hours to subtract from the Index to get the previous day's value depends on the KPI and Hr columns.
Yes that is correct
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |