Help with calculating a sum of a measure by weeknum

[william_johnson]

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. 

[mwegener]

Hi @william_johnson ,

was your problem solved?

[william_johnson]

No it wasn't. 

[mwegener · Accepted solution]

Hi @william_johnson 

I prefer Power Query. Take a look at the solution in the appendix.

[william_johnson]

Slaps head... geez it's so easy once you see it.

[william_johnson]

Still looking for ideas...

[v-easonf-msft]

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

[william_johnson]

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.

[amitchandak]

@william_johnson , refer to if my blog can help

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

[william_johnson]

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. 

[v-easonf-msft]

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 .

Pbix attached 

Best Regards,
Community Support Team _ Eason

 

[william_johnson]

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.

[v-easonf-msft]

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.

[william_johnson]

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

Daily New Cases =

VAR current_day = [Total Confirmed]

VAR prev_day =

CALCULATE (

[Total Confirmed],

DATEADD ( 'Calendar'[Date], -1, DAY )

)

RETURN

IF ( ISBLANK ( prev_day ), BLANK(), current_day - prev_day )

Total Confirmed is also a measure created to make the calculations easier I believe. 

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)

Everything works but the ability to sum up the current weeks totals... it just may not be possible, but I'm hoping it is. 

[william_johnson]

Any thoughts out there? I still haven't figured it out.