Sum previous complete week and return zero for schools that had no values in previous week

[Anonymous]

I am trying to sum the number of positive COVID cases at local schools for the most recent complete week (a complete week ending on Sunday). I update the data each week so I am trying to build a formula that updates the “new cases last week” value automatically.

The challenges:

• Some schools report new cases daily while others do not report any data for an entire week. For schools that did not report anything the previous week, my (broken) formula returns the value from the most recent week available for that school, even if it is not from the previous week. That is not what I want! If a school made no reports last week, I’d like the formula to return “0”.
• The MAX function and LASTDATE function filter to the last week of data in my dataset, but I run this report on Wednesday so the MAX date and LASTDate target the incomplete current week. I want the most recent complete week of data.

In the end, I want a table with a row for each school that shows the number of total cases and the number of new cases last week.
I tried many, many calculations. Here are a couple of my failures. I was trying to use minus 7 to bring me to the previous complete week. In the second example, I use a date table.

Example 1

NEW positive_ALL =

CALCULATE(SUM(schools[positive_all]), LASTDATE(schools[Week end date]-7))

Example 2

NEW positive_ALL =

CALCULATE(SUM(AllSchools[positive_ALL]),('AllSchools'[Week end date]= (MAX(Datetable[WeekEndDate]-7))))

Here is a link to some sample data I uploaded on WeTransfer. In the sample, there are at least two schools, Emma Willard and Catholic Central High, that did not submit data for the most recent week, which ended on Oct. 31.

Thanks for any guidance you may provide.

[Anonymous · Accepted solution]

@Anonymous 
Not sure how to calculate the new positive cases. But you would need to compare with weeknumber instead of date, since you want a full 7days from previous week.

You may create a new table with following dax:

Table = SUMMARIZE(schools,[School],"Last week total cases",CALCULATE(SUM(schools[positive_ALL]),FILTER(schools,WEEKNUM([EndofWeekColumn],2)=WEEKNUM(TODAY(),2)-1)))

[Anonymous]

I forgot to include the link to how we are using the data: https://www.timesunion.com/projects/2021/school-covid-tracker/