Max Value from Previous Week

[Caz_16]

Hello All,

Goal: What I want to do is calculate the change in cases per week (not in a percentage), just a raw number that is the number of new cases from the previous week (current week - previous week). I want this to happen for each week in my data. 

I have been working at this for a while and I am at a loss. I am doing COVID analysis for my local area using data from the VDH in csv format. The data comes in rows with each day being a new row and a new "Total Number of Cases", meaning its is a running sum of the total number of cases for the County.

I have been trying to figure out how to calculate the total number of new cases each week, from which I can calculate the % change. I feel like I have tried everything, see some examples below.

Measure:

Increase By Week 2 = 
VAR MostRecentDate = [Most Recent Date]

Return
CALCULATE(
    SUM('VDH-COVID-19-PublicUseDataset-Cases'[Total Cases]), 
    FILTER('Calendar Table', 'Calendar Table'[Date]= MostRecentDate
    )
)
-
CALCULATE(
    SUM('VDH-COVID-19-PublicUseDataset-Cases'[Total Cases]), 
    FILTER('Calendar Table', 'Calendar Table'[Date]= (MostRecentDate-7)
    )
)

Column:

Max By Week 2 = 
CALCULATE(
    MAXX(FILTER('VDH-COVID-19-PublicUseDataset-Cases', 'VDH-COVID-19-PublicUseDataset-Cases'[Week Number]),
    'VDH-COVID-19-PublicUseDataset-Cases'[Total Cases])
    )

Measure

Measure 2 = 
VAR lastWeekAmount =
    CALCULATE (
        SUM ( 'VDH-COVID-19-PublicUseDataset-Cases'[Total Cases] ),
        FILTER ('VDH-COVID-19-PublicUseDataset-Cases',
            'VDH-COVID-19-PublicUseDataset-Cases'[Week Number]
                = MIN ( 'VDH-COVID-19-PublicUseDataset-Cases'[Week Number] ) - 1
        )
    )
RETURN
    SUM ( 'VDH-COVID-19-PublicUseDataset-Cases'[Total Cases] ) - lastWeekAmount

 Column

(This one, I figured if I could  get the day-by-day change, I could sum them by week number to get the total)

Day Change = 
Var MostRecentDate = [Most Recent Date]
RETURN
[Most Recent Case Total]-CALCULATE(SUMX('VDH-COVID-19-PublicUseDataset-Cases','VDH-COVID-19-PublicUseDataset-Cases'[Total Cases]), FILTER('Calendar Table',[Date]=MostRecentDate-1))

 Measure 

Measure = 
VAR __max = MAX('Calendar Table'[Date])
RETURN
calculate(MAXX('VDH-COVID-19-PublicUseDataset-Cases','VDH-COVID-19-PublicUseDataset-Cases'[Total Cases]) ,
filter('Calendar Table', 'Calendar Table'[Week Number]-1 ))

Here is what I understand, and dont understand.

- I understand and know how to create a variable that gets JUST the prevous week's total (or the maximum on the last day of the week, because cases aren't going down, they only go up), which in my case is the single value of 91. Then subtract that from the current week's maximum and you get 3 (94-91), and from there I can calculate % change. 

- What I DO NOT UNDERSTAND (and it is driving me insane) is why I cannot create a calculated column where I find the

MAX([Total Cases])FILTER(Calendar Table, [Week Number] -1). This in my mind should be the maximum value of a table where the week number is one less than the row being evaluated. Any combination of formulas where I try and find [Week Number] - 1 always evaluates to the current week and not the week prior. 

PBIX and Data Link to Google Drive = https://drive.google.com/drive/folders/1T37f9r-MjjS7ZNFDzueLyOUMiXT_VfWH?usp=sharing

[v-yingjl · Accepted solution]

Hi @Caz_16 ,

If you want to calculate the cases between current week and last week which based on current week, you can try this measure:

Change by week =
VAR _Week =
    WEEKNUM ( TODAY (), 2 )
VAR thisweeknumber =
    CALCULATE (
        SUM ( 'VDH-COVID-19-PublicUseDataset-Cases'[Total Cases] ),
        'VDH-COVID-19-PublicUseDataset-Cases'[Week Number] = _Week
    )
VAR lastweeknumber =
    CALCULATE (
        SUM ( 'VDH-COVID-19-PublicUseDataset-Cases'[Total Cases] ),
        'VDH-COVID-19-PublicUseDataset-Cases'[Week Number] = _Week - 1
    )
RETURN
    thisweeknumber - lastweeknumber

Based on your dataset, it will get the result like this:

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Caz_16 , refer my blog on week comparison if that can help

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[lbendlin]

yeah, contexts are hard.

Try to save the current week number into a variable BEFORE you start with Calculate.