Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am probably showing my ignorance in this question expecting it to work when it doesn't :
I have a measure to lift Line Value from a SQL table Invoices
Sales Value = sum(Invoices[Line_Value])
I have Var in each further measure for
Reporting Week_TY / Reporting_Year_TY / Reporting Week_LY / Reporting_Year_LY
(where TY and LY are this year and last year respectively)
which has been suggested to me I change to measures too but that is a separate issue.
This then allows me to do further measures such as Example 1 to 2 where the only difference is whether I am calculating weeks weeks this year or Last year in the 'Calc' bit. I'd like to to maintain my code integrity as I have other measures such as one for Profit that says - inevitably -
Profit = sum(Invoices[Profit])
so all I have to do is change my respective codes from [Sales Value] to [Profit]
Everything works swimmingly to calculate
Wholesale Sales Week This Year / Wholesale Sales Week Last Year
with a slicer for the year and week number which of course feeds in to
VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])
the problem is that the users now want to choose multiple weeks on the slicer so that if they choose weeks 15 / 16 / 17, say, the this year and last year are the sum of the chosen weeks for this year and last year
When I do it, this year is zero and last year is a sum which is not the combination
****************************
// Example 1
Wholesale Sales Week This Year =
VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])
VAR Use_Week = Selected_Week - [_Relative_Date_Offset Calc]
VAR Selected_Year = SELECTEDVALUE(Dates[Year Int])
VAR Reporting_Week_TY = Use_Week
VAR Reporting_Week_LY = Reporting_Week_TY - 1
Var Reporting_Year_TY = Selected_YEAR
Var Reporting_Year_LY = Reporting_Year_TY - 1
// The Calc bit :
VAR Hold_Calc = SUMX( FILTER(ALL(Dates),
Dates[Week_Int]= Reporting_Week_TY && Dates[Year Int] = Reporting_Year_TY),
[Sales Value])
Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)
*************************************
// Example 2
Wholesale Sales Week Last Year =
VAR Selected_Week = SELECTEDVALUE(Dates[Week_Int])
VAR Use_Week = Selected_Week - [_Relative_Date_Offset Calc]
VAR Selected_Year = SELECTEDVALUE(Dates[Year Int])
VAR Reporting_Week_TY = Use_Week
VAR Reporting_Week_LY = Reporting_Week_TY - 1
Var Reporting_Year_TY = Selected_YEAR
Var Reporting_Year_LY = Reporting_Year_TY - 1
// The Calc bit :
VAR Hold_Calc = SUMX( FILTER(ALL(Dates),
Dates[Week_Int]= Reporting_Week_LY && Dates[Year Int] = Reporting_Year_LY),
[Sales Value])
Return IF ( Hold_Calc = BLANK(),0, Hold_Calc)
****************
Thank you.
Unfortunately that went right over my head at the moment 😞
I was hoping to change my code slightly to maintain the integrity and re-usability with all my date VAR and subsequent calcs where in the 'Calc bit' above "all" I had to do was change the date range and what I was scanning for (Sales / Profit / Budget etc)
Took me an absolute age to get to where I am now. They said they wanted Week / Period / YTD for Sales / Profit versus last year and versus Budget and now they don't. Or rather it is "Yes, but........."
PS: I have your book 🙂
I've been written to by someone famous!
Last time that happened to me was when Miriam Margolyes wrote to me on CompuServe, he says showing his age.
@Steve_Scotland You can't use SELECTEDVALUE when multiple values are selected. You will want to use SELECTCOLUMNS to to return your values as a single column table. You could also use DISTINCT (column form) or VALUE (not recommended). You can then use the IN operator against this table var to filter on.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
26 |