cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted

@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!






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

7 REPLIES 7
Highlighted
Super User IV
Super User IV

@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!






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.





Highlighted

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

Highlighted

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

 

 






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.





Highlighted

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

Highlighted

@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






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.





Highlighted

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

Highlighted

@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!






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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors