"X Weeks Ago" Parameter for Revenue Comparison

[Anonymous]

Hi all,

I want to introduce a parameter into my model that allows the user to select their weekly comparison value. I want them to have the option to select between a 1 week ago view and a 52 week ago view. The final table would look something like this:

Actuals	Prior Year	Var Act/PY Wk	Comparison Week	Var Act/Comparison
£114,274.69	£102,326.73	£11,947.96	£126,391.00	-£12,116.31

Where the comparison week values change based on the users input into the parameter filter.

I've been able to acomplish this easily for months and weeks (in the same year), however, my problems arise when I'm in week 10 of a particular year and I want to compare my current week to anything >= 10 weeks ago (into the previous financial year).

Below is my syntax for months which is working fine:

Previous Mth Rev = CALCULATE([Revenue],DATEADD('MD_Calendar'[Date], - MonthComparison[MonthComparison Value], MONTH))

Below is my syntax for weeks which is working (apart from when I try to cross into another year):

Previous Wk Rev = 
VAR Current_Week = SELECTEDVALUE('MD_Calendar'[Ops_Week_Number])
VAR Current_Year = SELECTEDVALUE('MD_Calendar'[Ops_Year])
VAR Max_Week_Number = CALCULATE(MAX('MD_Calendar'[Ops_Week_Number]),ALL('MD_Calendar'))
RETURN
SUMX(FILTER(ALL('MD_Calendar'),
    IF(Current_Week = 1, 
        'MD_Calendar'[Ops_Week_Number] = Max_Week_Number && 'MD_Calendar'[Ops_Year] = Current_Year - WeekComparison[WeekComparison Value],
            'MD_Calendar'[Ops_Week_Number] = Current_Week - WeekComparison[WeekComparison Value] && 'MD_Calendar'[Ops_Year] = Current_Year)),
                    [Revenue])

Any advice on how to make this parameter work across years would be very much appreciated!

Cheers,

Aaron

[tex628]

Is it possible to use the same dateadd but with days instead months? Since every week is going to be 7 days it should jsut be a calculation of [Selected week]*7. 

[Anonymous · Accepted solution]

Hi Tex,

Thanks for the reply. That method works if your calendar has weeks that all have 7 days. Unfortunately I'm working with a custom calendar that doesn't work like that. On top of that, my years can sometimes have 52 weeks or 53 weeks.

I managed to come up with this solution that works if you're interested to see:

Comparison Week = 
VAR Current_Week = SELECTEDVALUE('Calendar'[Week_Number])
VAR Current_Year = SELECTEDVALUE('Calendar'[Year])
VAR Max_Week_Number = CALCULATE(MAX('Calendar'[Week_Number]),FILTER(ALL('Calendar'), 
                        Calendar[Year] = SELECTEDVALUE(Calendar[Year]) - 1))
RETURN
SUMX(FILTER(ALL('Calendar'),
    IF([WeekComparison Value] >= Current_Week, 
        'Calendar'[Week_Number] = Max_Week_Number - (WeekComparison[WeekComparison Value]-Current_Week)
            && 'Calendar'[Year] = Current_Year -1,
            'Calendar'[Week_Number] = Current_Week - WeekComparison[WeekComparison Value]
                && 'Calendar'[Year] = Current_Year)),
                    [Revenue])

Cheers,

Aaron