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))
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)
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())
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.
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!
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!
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.
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.
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.
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.
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.
Everybody is in “still discovering” phase when talking about DAX @Melissa
Even if you understand the concepts well … every situation is providing new ways of combining functions or Data Modelling techniques