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.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |