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
bdmichael09
Helper II
Helper II

Problem with calculated column getting previous day value

Hello everyone,

 

I'm having what I hope is a fairly simply problem but I can't quite seem to figure it out. I'm trying to play around with a publicly available COVID dataset but the data simply has the total number of cases/deaths for each state on a given calendar day. What I am trying to do is create a calculated column to tell me how many cases a given state had on X day. Seems fairly straight forward, I just need to be able to know what "today's" date is, "yesterday's date", and then calculate the value of Total Cases today and subtract what that Total cases number was yesterday. Everything goes well up to the point that I try to get yesterday's Total Cases and I just keep getting the same number as Todays Cases. I'm hoping someone can point out where my reasoning or DAX is flawwed here. 

 

 

Daily Cases = 
var mindate = calculate(min('us-states'[date]),filter('us-states','us-states'[state] = earlier('us-states'[state])))
var todaysdate = 'us-states'[date]
var yesterdaysdate = PREVIOUSDAY('us-states'[date])
var totalcases = 'us-states'[Total Cases]
var totalcasesyesterday = calculate(totalcases,filter('us-states','us-states'[date] = yesterdaysdate))
var casestoday = totalcases - totalcasesyesterday
return
casestoday

 

The dataset can be found here: https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv 

1 ACCEPTED SOLUTION

@bdmichael09 solution attached.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@FarmerKenny glad to hear that this solution helped. Do subscribe to my YouTube channel where I post videos to provide solutions to similar use cases.

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

FarmerKenny
Helper II
Helper II

This solution saved me 2 days of trying and failing.  Thanks much for your solution, @bdmichael09!

parry2k
Super User
Super User

@bdmichael09 As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

 

Once you have date dimension, put date on the x-axis from date dimension and count from your data table, and you will get count by each day and from here, any time intelligence-based calculation will be super easy.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I have date in the data. I also have a full date table already. That isn't the problem.

 

I'll reiterate, the columns in the data with case count and death count are just totals UP TO x day. So if a case count number says 1 on 2/14 that means there had only been 1 case in X state on 2/14. If that number jumps to 5 on 2/15, 4 cases were confirmed on 2/15 and I'm trying to get that 4 number (the difference between 2/14 and 2/15). This can't be done simply by tossing Total Cases in a chart. 

@bdmichael09 it means you need to have in the data model to identify the person or something to tell it is a new case, it is like typical calculation of calculating, new customer vs returning customer something like this post.

 

I would recommend to share sample data and expected output to help provide the solution. All of it depends on how your data looks like, it is not a simple time intelligence calculation.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k The entire dataset is linked in my first post. As to what I'm looking for, I expect something like the following:

 

image.png

What doesn't exist currently is the Daily cases column. That's what I'm trying to generate. 

@bdmichael09 something like this I guess? The other two measures are just for reference but the last one is the one you want, correct?

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  100%. That is exactly what I am looking for. 

@bdmichael09 solution attached.

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.