Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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.
Solved! Go to 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:
@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:
I forgot to include the link to how we are using the data: https://www.timesunion.com/projects/2021/school-covid-tracker/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |