Previous Week Total has no values in my table visual

[jennylouf]

Hello everyone,

Appreciate your expertise in making my measures work.

Here’s the table named ds-collab:

Inquiry_CCC	Symptom_CCC	Topic	CaseNumber	CreatedDate
Rewards Program	Customer Account Reinstatement Requests Due to App Access Issues	Customer Account Reinstatement Issues	6105820	09/04/2024
Rewards Program	Customer Account Reinstatement Requests Due to App Access Issues	Customer Account Reinstatement Requests Due to App Access Issues	6102470	09/03/2024
Rewards Program	Customer Points Issues and Resolutions	Customer Points Discrepancies	6106142	09/05/2024
Rewards Program	Customer Points Issues and Resolutions	Customer Rewards Program Inquiries and Phone Number Issues	6106029	09/04/2024
Rewards Program	Suspended Customer Accounts Due to Fraud	Customer Account Lock Issues	6103346	09/03/2024
Rewards Program	Suspended Customer Accounts Due to Fraud	Customer Account Suspension Due to Fraud	6105990	09/04/2024
Mobile Payment App	Credit Card Linking Issues and AVS Mismatches	AVS Mismatch Issues with Debit Card Linking	6106257	09/05/2024
Mobile Payment App	Credit Card Linking Issues and AVS Mismatches	Techron Advantage Card App Issues	6104759	09/04/2024
Mobile Payment App	Customer Issues with Linking eGC/Gift Cards Due to Active Card Limits	Customer Issues with Linking eGC/Gift Cards Due to Active Card Limits	6103373	09/03/2024
Mobile Payment App	Customer Login Issues on New Phones Resolved by Reinstalling App	Customer Login Issues on New Phone Resolved by Reinstalling App	6106158	09/05/2024
Mobile Payment App	Customer Login Issues on New Phones Resolved by Reinstalling App	Phone Number Update Requests	6106221	09/05/2024
Mobile Payment App	Customer Refund Requests for Gas Charges	Customer Refund Requests for Gas Charges	6106473	09/05/2024
Mobile Payment App	Customer Refund Requests for Gas Charges	Venmo Payment Issues and Customer Support	6105942	09/04/2024
Mobile Payment App	Daily Transaction Limit Exceeded	Daily Transaction Limit Exceeded	6105171	09/04/2024
Mobile Payment App	Gift Card Linking Issues Due to Active Card Limits	Gift Card Linking Issues Due to Active Card Limits	6105686	09/04/2024
Mobile Payment App	Gift Card Linking Issues in App	EGC Linking Issues	6105997	09/04/2024
Mobile Payment App	Gift Card Linking Issues in App	Gift Card Addition Issues	6105884	09/04/2024
Mobile Payment App	HDA Velocity Error Retry Instructions	eGC Velocity Linking Issues	6103229	09/03/2024
Mobile Payment App	HDA Velocity Error Retry Instructions	HDA Velocity Error Retry Instructions	5977645	07/03/2024
Mobile Payment App	Login Error with Verification Code	Code Entry Error Messages	6099092	09/01/2024
Mobile Payment App	Login Error with Verification Code	Login Issues Due to Verification Code Errors	6105773	09/04/2024
Mobile Payment App	Mobile Payment App Issues	Mobile Payment App Issues	6106301	09/05/2024
Mobile Payment App	Mobile Payment App Issues	Responsible AI Policy - Flagged Descriptions	6106241	09/05/2024
Mobile Payment App	Mobile Payment Issues and Solutions	Payment Declination Issues	6106034	09/04/2024
Mobile Payment App	Mobile Payment Issues and Solutions	Payment Issues with App	6106282	09/05/2024
Mobile Payment App	Payment Rate Limit Exceeded	Payment Rate Limit Exceeded	6105833	09/04/2024
Mobile Payment App	Pre-Authorization Hold Customer Issues	Customer Inquiries on Failed Transactions and Refunds	6106019	09/04/2024
Mobile Payment App	Pre-Authorization Hold Customer Issues	Gas Charge and Pre-Authorization Hold Issues	6106481	09/05/2024

There are slicers for year (single select) and month (multi select) based on a "_Calendar" table. 

I created a measure for CurrentWeekTotal that should be based on the selected year and if month is not selected or all are selected it will give the current week total (as of the last CreatedDate), but if a month or multiple months is selected it will give the last week total as base for current.

These seems to work:

TotalCases = DISTINCTCOUNT('ds-collab'[_CaseNumber])

CurrentWeekTotal = 
VAR SelectedYear = SELECTEDVALUE('_Calendar'[Year])
VAR SelectedMonths = VALUES('_Calendar'[MonthNo])
VAR LastCreatedDate = MAX('ds-collab'[CreatedDate])
VAR CurrentWeek = WEEKNUM(LastCreatedDate, 2)
RETURN
IF( ISFILTERED('_Calendar'[Month]) && COUNTROWS(SelectedMonths) > 0,
    CALCULATE(
        [TotalCases],
        FILTER(
            'ds-collab',
            WEEKNUM('ds-collab'[CreatedDate], 2) = CurrentWeek &&
            YEAR('ds-collab'[CreatedDate]) = SelectedYear &&
            MONTH('ds-collab'[CreatedDate]) IN SelectedMonths )),
    CALCULATE(
        [TotalCases],
        FILTER(
            'ds-collab',
            WEEKNUM('ds-collab'[CreatedDate], 2) = CurrentWeek &&
            YEAR('ds-collab'[CreatedDate]) = SelectedYear )))

Now, I need to create the next measures, but cannot proceed as I'm getting blank values in my table for the PreviousWeekTotal.

1. previous week total is one week prior the CurrentWeekTotal
2. variance % of current week and previous week total
3. average weekly total
4. average previous weekly total is one week prior the average weekly total
5. variance % of average weekly total and average previous weekly total

Can I have the measures for the remaining 5?  

Here's what I did for the PreviousWeekTotal that gives me blank values in my table visual.

PreviousWeekTotal = 
VAR SelectedYear = SELECTEDVALUE('_Calendar'[Year])
VAR SelectedMonths = VALUES('_Calendar'[MonthNo])
VAR LastCreatedDate = MAX('ds-collab'[CreatedDate])
VAR CurrentWeek = WEEKNUM(LastCreatedDate, 2)
VAR PreviousWeek = CurrentWeek - 1
RETURN
IF( ISFILTERED('_Calendar'[Month]) && COUNTROWS(SelectedMonths) > 0,
    CALCULATE(
        [TotalCases],
        FILTER(
            'ds-collab',
            WEEKNUM('ds-collab'[CreatedDate], 2) = PreviousWeek &&
            YEAR('ds-collab'[CreatedDate]) = SelectedYear &&
            MONTH('ds-collab'[CreatedDate]) IN SelectedMonths )),
    CALCULATE(
        [TotalCases],
        FILTER(
            'ds-collab',
            WEEKNUM('ds-collab'[CreatedDate], 2) = PreviousWeek &&
            YEAR('ds-collab'[CreatedDate]) = SelectedYear )))

Appreciate your input.  Thanks for your time.

[lbendlin]

VAR PreviousWeek = CurrentWeek - 1

That will not work when you are in week 1 of a year.

Instead, use DATEADD(<calendar date>,-7,DAY)