Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have been messing around with a report on Covid 19 cases based on ideas created by another user on the internet. From the Johns Hopkins data it shows the total confirmed and total deaths for each day. I use a measure to determine the daily new cases. I would like to also create a measure to calculate the new cases by the weeknumber. I have a calendar table.
My data looks like this:
Date | Combined_Key | Total Confirmed | Week Number | Measure(Daily New Cases) |
3/4/2020 | New York City, New York, US | 1 | 10 | |
3/5/2020 | New York City, New York, US | 4 | 10 | 3 |
3/6/2020 | New York City, New York, US | 11 | 10 | 7 |
3/7/2020 | New York City, New York, US | 11 | 10 | 0 |
3/8/2020 | New York City, New York, US | 12 | 10 | 1 |
3/9/2020 | New York City, New York, US | 19 | 11 | 7 |
3/10/2020 | New York City, New York, US | 25 | 11 | 6 |
3/11/2020 | New York City, New York, US | 55 | 11 | 30 |
3/12/2020 | New York City, New York, US | 95 | 11 | 40 |
3/13/2020 | New York City, New York, US | 154 | 11 | 59 |
3/14/2020 | New York City, New York, US | 269 | 11 | 115 |
3/15/2020 | New York City, New York, US | 269 | 11 | 0 |
The answer I am looking for is a total cases in week 10 of 12, and a total cases in week 11 of 257. To be clear the last column is a measure. The first three columns is what the data looks like from Johns Hopkins University.
I'm not sure if I should be doing this in the query or in DAX in the report.
Thanks for your assistance.
Solved! Go to Solution.
I prefer Power Query. Take a look at the solution in the appendix.
Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
Hi @william_johnson ,
was your problem solved?
Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
No it wasn't.
I prefer Power Query. Take a look at the solution in the appendix.
Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials
Still looking for ideas...
Hi, @william_johnson
Can you share part of the data(the Johns Hopkins data ) corresponding to the original table?
It will be difficult for us to conduct further analysis.
Best Regards,
Community Support Team _ Eason
I'll do you one better, here is the data source: https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_ti...
I use a measure to get to the new cases by creating the total confirmed measure, and then subtracting yesterday's total confirmed from today's (basically). I want to somehow (as I noted, be able use total the daily new cases by weeknumber.
@william_johnson , refer to if my blog can help
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
Not sure how to apply this to my question. I think my issue is that I am calculating the daily cases as a measure, not as a column. I am concerned that the weeknum and the such require a column.
Hi, @william_johnson
Calculated column will be a better choice.
Please try dax as below:
C_weeknum = WEEKNUM('Table_C'[Date]-1)
new cases added =
var previous_confirmed= CALCULATE(SUM('Table_C'[Total Confirmed]),PREVIOUSDAY('Calendar'[Date]))
return IF(previous_confirmed=BLANK(),BLANK(),'Table_C'[Total Confirmed]-previous_confirmed)
Please check the sample file .
Best Regards,
Community Support Team _ Eason
So my Total Confirmed is a meausre as well. When I try to turn it into a column and use the nca column I get the dreaded circular dependency issue.
In the table there is a confirmed, and to get the total confirmed by the combined_key field which is the County and State a measure was created:
Total Confirmed =
VAR sum_confirmed=
Sumx(
Values('Covid 19 US Data'[Province_State]),
SUMX(
Values('Covid 19 US Data'[Combined_Key]),
Calculate(Max('Covid 19 US Data'[Confirmed]))
)
)
Return
if(sum_confirmed=0,Blank(), sum_confirmed)
I'm thinking I need to add in that language to the NCA formula as well, but I also need the total confirmed... I'm a newbie. Thanks for your assistance.
Hi , @william_johnson
Can you share part of the data corresponding to the original table?
Is "Total Confirmed" grouped by fields [Province_State] and field [Combined_Key] to sum up [Confirmed]?
If yes , you can try calculate column as below:
Total Confirmed column =
CALCULATE (
SUM ( 'Covid 19 US Data'[Confirmed] ),
'Covid 19 US Data',
'Covid 19 US Data'[Combined_Key] = EARLIER ( 'Covid 19 US Data'[Combined_Key] ),
'Covid 19 US Data'[Province_State]
= EARLIER ( 'Covid 19 US Data'[Province_State] )
)
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-easonf-msft Thanks for the assistance let me try and get a couple lines of data: The Your Total Confirmed is the results I get from your code.
Date | Province_State | Combined_Key | Confirmed | Total Confirmed | Daily New Cases | Your Total Confirmed |
3/2/2020 0:00 | New York | New York City, New York, US | 1 | 1 | 20918754 | |
3/3/2020 0:00 | New York | New York City, New York, US | 1 | 1 | 0 | 20918754 |
3/4/2020 0:00 | New York | New York City, New York, US | 1 | 1 | 0 | 20918754 |
3/5/2020 0:00 | New York | New York City, New York, US | 4 | 4 | 3 | 20918754 |
3/6/2020 0:00 | New York | New York City, New York, US | 11 | 11 | 7 | 20918754 |
3/7/2020 0:00 | New York | New York City, New York, US | 11 | 11 | 0 | 20918754 |
3/8/2020 0:00 | New York | New York City, New York, US | 12 | 12 | 1 | 20918754 |
3/9/2020 0:00 | New York | New York City, New York, US | 19 | 19 | 7 | 20918754 |
3/10/2020 0:00 | New York | New York City, New York, US | 25 | 25 | 6 | 20918754 |
3/11/2020 0:00 | New York | New York City, New York, US | 55 | 55 | 30 | 20918754 |
3/12/2020 0:00 | New York | New York City, New York, US | 95 | 95 | 40 | 20918754 |
Daily New Cases Measure:
Any thoughts out there? I still haven't figured it out.
User | Count |
---|---|
144 | |
71 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |