cancel
Showing results for
Did you mean:
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
Super User III

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

7 REPLIES 7
Super User III

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

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!

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

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

Super User III

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

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

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

Super User III

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper II

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

Super User III

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!