Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX FOR CREATING A COLUMN TO DISPLAY DATA FROM THE PREVIOUS DAY

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?

9 REPLIES 9
Sahir_Maharaj
Super User
Super User

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].


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Anonymous
Not applicable

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 🙂


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Anonymous
Not applicable

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" 🙂


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Anonymous
Not applicable

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.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Sahir_Maharaj
Super User
Super User

 

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.

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Anonymous
Not applicable

Yes that is correct

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.