Forum Discussion
Problem with calculated column getting previous day value
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!
Super UserAnonymous 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!
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.
- parry2k
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.
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.
- parry2k
Anonymous something like this I guess? The other two measures are just for reference but the last one is the one you want, correct?