Forum Discussion
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
Anonymous 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!
9 Replies
Anonymous 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-columnsOnce 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!
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.
Anonymous 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.
This solution saved me 2 days of trying and failing. Thanks much for your solution, Anonymous!
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 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.
