Cumulative sales through same week last year

I’ve been working and reviewing forum posts on comparing sales to prior periods on a weekly basis and have been getting the Cumulative and the SUMX formulas to work. But what I’m trying to do is slightly different: I need to compare sales on a cumulative basis through the same week last year. So I think I need to nest the SUMX inside the CALCULATE function, but I’m getting errors. Here’s my formula:

YTD Sales thru this Week - Prior Year (CUM) =
VAR CurrentWeek = SELECTEDVALUE( Dates[Week Number] )
VAR CurrentYear = SELECTEDVALUE( Dates[Year] )
VAR MaxWeekNum = CALCULATE( MAX(Dates[Week Number]), ALL(Dates))

RETURN
CALCULATE( [CurrentSales],
    SUMX(
        FILTER( ALL(Dates),
        IF(CurrentWeek = 1,
            Dates[Week Number] = MaxWeekNum && Dates[Year] = CurrentYear - 1,
            Dates[Week Number] = CurrentWeek && Dates[Year] = CurrentYear - 1)),
    [CurrentSales])
Dates[Date] <= MAX( Dates[Date] ) ))

I am receiving a SYNTAX error on the first Dates[Date] statement on the last line of my formula, and I’m not sure why. Screenshot attached.Screenshot 5-11-20.pdf (158.7 KB)

Does this work ?

Sales CY Running Total 
= 
VAR MaxDate = MAX ( 'Dates'[Date] ) -- Saves the last visible date
RETURN
    if (MAX(Dates[WeekNo]) < WEEKNUM(TODAY(),2),
    CALCULATE (
        [Total Sales],            
        'Dates'[Date] <= MaxDate,   
        ALL ( Dates )               
    ),Blank())

Sales Previous Year

CALCULATE(
[Total Sales],
DATESYTD(SAMEPERIODLASTYEAR ( ‘Dates’[Date] ))

)

Good Day Silva,

Please watch this tutorial from @sam.mckay to acheive this, where he has explained in detail.

If you need additional help please share some data sample and expected output.

Thanks for posting your question @kjssdca. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

No, that doesn’t work because it uses SAMEPERIODLASTYEAR, when what I am trying to compare is sales by week, not by date.

I see two issues with your measure, 1st you’re missing a comma after the closing parenthesis of SUMX and 2nd you’ve entered an iterator returning a scalar value, not a filter condition as the second argument of CALCULATE.

.

Okay, what kind of logic are you using for the Dates[Week Number]? please share your date table.
And so I’m clear on your requirement. Let’s say the CurrentWeek equals 2020-19 and it’s a Wednesday

  • Sum all Sales from: 2020-01 ~ 2020-19 AND subtract all Sales from weeks: 2019-01 ~ 2019-19 (result. potentially comparing a partial- with a completed week 19 from the previous year)
  • Sum all Sales from: 2020-01 ~ 2020-18 AND subtract all Sales from weeks: 2019-01 ~ 2019-18 (comparing only completed weeks)
  • Other …

How do you want to visualize the result in your report?
Please provide a Sample PBIX so members can better assist you, thanks!

1 Like

Hello @kjssdca,

You might find very useful these three links:

There are some refferences to Custom Time intelligence tricks that might solve your problem.

If you liked my solution please give it a thumbs up :+1:.

If I did answer your question please mark my post as a solution :white_check_mark:.

Thank you !

Cristian

1 Like

@kjssdca

Hello - so i my dax query i made a slight adjustment

Sales CY Running Total = 
VAR MaxDate = MAX ( 'Dates'[Date] ) -- Saves the last visible date
RETURN
    if (MAX(Dates[WeekNo]) < WEEKNUM(TODAY(),2),
    CALCULATE (
        [Total Sales],            -- Computes sales amount
        'Dates'[Date] <= MaxDate,   -- Where date is before the last visible date
        ALL ( Dates )  ,             -- Removes any other filters from Date
        Year(MaxDate) = Year(Dates[Date])
    ),Blank())

pls refer attached snapshot… each row represents the week… is this not what is needed?

Thank you @Melissa. Adding the comma after the closing parenthesis of SUMX cleared things up a bit. The error now reads, “A function ‘SUMX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.” - I think it’s basically talking about the same thing you mention about my second argument in CALCULATE, which I am still learning about. I am uploading a write-up of my expected output. My dates table is just the download from this site, to which I have added a “Year & Week” column per one of Sam’s videos so the 12th week of year 2020 will be displayed as: 2020-12. I added the column because our company does a TON of analysis comparing weeks between years, so it seemed like a good step to take.

Below is a mock-up of expected output. I don’t have a PBIX file because it is real data, which I’m not at liberty to share and don’t have the time to mock something up, so bear with me as I dig through the layers of the issue, learning every step of the way! :blush:
Expected Output_Cum Weekly Sales

@cristian.angyal Thanks for the links! They have helped me to understand CALCULATE and context a bit more. Very helpful!

1 Like

Hi @kjssdca,

Here’s an article I found helpful.

Applied that to a sample, here the current year.

And for the previous year.

It looks promising, here’s my sample file.
Cumulative Week over Week PY v2.pbix (452.4 KB)

.

UPDATE @kjssdca
Attached a new version of the file, the solution is solid IMO but thinking back I had to modify the Day of Year . So that’s what’s changed. The Dates table now contains ISO Year, ISO Week and ISO Day numbers on which these calculations rely.

I hope this is helpful.

1 Like

Hi @kjssdca,

Let’s go over what changed to compare Weeknumber CY with the same Weekno in the LY.

  1. Updated the Date table M function to include ISO Year, ISO Week and ISO Day of Year, you can find that here: Extended Date Table (Power Query M function)

    NOTE. If your business logic for Weeknumbers is different, you’ll have to update the logic for columns that contain ISO* in the name. Doing that will affect other columns in the Date table.
  2. The most important addition to the Date table for your requirement is ISO Day of Year. This counts the days in the ISO Year and is therefore at the same granularity as the Date column but at an offset to it, because it always starts on the first Monday in ISO week no 1 (full calendar years). This alignment is what makes the same week comparison over years possible.
  3. The basic cumulative pattern: FILTER( ALL( Dates[Date] ), Dates[Date] <= MAX( Dates[Date] )) could be replaced by: FILTER ( ALL( Dates[ISO Day of Year] ), Dates[ISO Day of Year] <= MAX( Dates[ISO Day of Year] )) although we’ll also incorporate the ISO Year in the full pattern.
  4. please review this article (also referenced above): Week based time intelligence

Here are the measures created:

Sales Current Week = 
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
      Dates[ISO Weeknumber] = SELECTEDVALUE( Dates[ISO Weeknumber] ) && 
      Dates[ISO Year] = SELECTEDVALUE( Dates[ISO Year] )
    )
)

.

Same Week Last Year = 
IF( HASONEVALUE( Dates[ISO Year] ) && HASONEVALUE( Dates[ISO Weeknumber] ), 
    CALCULATE( [Total Sales],
        FILTER( ALL( Dates ),
            Dates[ISO Year] = VALUES( Dates[ISO Year] )-1
                && Dates[ISO Weeknumber] = VALUES( Dates[ISO Weeknumber] )
                && Dates[Date] <= MAX( Dates[Date] )
        )
    ))

.

Cum Sales CY = 
    CALCULATE( [Total Sales], 
        FILTER( ALL(Dates[Date]), 
            Dates[Date] <= MAX(Dates[Date])), 
        VALUES(Dates[ISO Year])
    )

.

Cum Sales LY = 
IF( HASONEVALUE( Dates[ISO Year] ),
    CALCULATE( [Total Sales],
        FILTER( ALL( Dates ),
            Dates[ISO Year] = VALUES ( Dates[ISO Year] ) -1 &&
            Dates[ISO Day of Year] <= MAX( Dates[ISO Day of Year] )
        )
    ))

.
I hope this is helpful. File with updated Date table below.
Cumulative ISO Week vs ISO Week LY.pbix (440.2 KB)

2 Likes

Hi @kjssdca, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

@Melissa thanks so much for the thorough response, which I am reviewing. I must admit, I find the switch from DAX to M and ISO dates a bit overwhelming, when I am still struggling with basic DAX functions, such as CALCULATE. But I think I am getting “my head around” CALCULATE better since last week, thanks to this forum.

Awesome. :+1:

Glad to hear you are making progress. It can be a lot to take in when you are just getting started… I still discover something new every day. :wink:

1 Like

Everybody is in “still discovering” phase when talking about DAX @Melissa :stuck_out_tongue_winking_eye:
Even if you understand the concepts well … every situation is providing new ways of combining functions or Data Modelling techniques

1 Like

@cristian.angyal,

True that. Even the guys who literally wrote the DAX bible admit they’re still learning…

  • Brian
1 Like

I was referring to them … and all the rest of us, as well :stuck_out_tongue_winking_eye: