Forum Discussion
Selectedvalue in a Switch measure not selecting dates correctly
Hi team
I have created a calculated column that looks for a particlar company, and then multiplies it net sales by a set amount based on a date range. This column works fine, however I need it to work as a measure too. The calculated column is:
x-Updated Sales Switch = SWITCH(
True(),
'# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2023,01,01) && '# Sales'[Invoice Date] <= DATE (2023,03,31), '# Sales'[Net Sales Amount]*1.4997,
'# Sales'[Company Code] = "pus" && '# Sales'[Invoice Date] >= DATE (2022,10,01) && '# Sales'[Invoice Date] <= DATE (2022,12,31), '# Sales'[Net Sales Amount]*1.5302,
'# Sales'[Net Sales Amount])
x-Updated Sales Measure = SWITCH(
True(),
SELECTEDVALUE('# Sales'[Company Code]) = "pus" && SELECTEDVALUE('# Sales'[Invoice Date]) >= DATE(2023,01,01) && SELECTEDVALUE('# Sales'[Invoice Date]) <= DATE(2023,03,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.4997),
SELECTEDVALUE('# Sales'[Company Code]) = "pus" && SELECTEDVALUE('# Sales'[Invoice Date]) >= DATE(2022,10,01) && SELECTEDVALUE('# Sales'[Invoice Date]) <= DATE(2022,12,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.5302),
SUM('# Sales'[Net Sales Amount])
)
I did some testing, and removed the dates part of the measure, and I got the correct results (ie "pus" company 'net sales amount' is multiplied by the correct amount). Note - the "pil" company does not need to be multiplied, so I left it as x1 is the below measure:
x- update sales meas v2 = SWITCH(
True(),
SELECTEDVALUE('# Sales'[Company Code]) = "pus", SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.4997),
SELECTEDVALUE('# Sales'[Company Code]) = "pil", SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.0),
SUM('# Sales'[Net Sales Amount])
)
Because of this, I assume the "&& SELECTEDVALUE('# Sales'[Invoice Date]) >= DATE(2023,01,01) && SELECTEDVALUE('# Sales'[Invoice Date]) <= DATE(2023,03,31)" part of my measure is incorrect, and Power BI is not picking up the dates correctly.
Note: Date table & #Sales table are connected by 'Date'[DateKey] and '#Sales'[Invoice Date].
Is anyone able to assist to see where I am going wrong?
LewisB , Create a measure like
x-Updated Sales Measure = Sumx('# Sales' , SWITCH(
True(),
('# Sales'[Company Code]) = "pus" && ('# Sales'[Invoice Date]) >= DATE(2023,01,01) && ('# Sales'[Invoice Date]) <= DATE(2023,03,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.4997),
('# Sales'[Company Code]) = "pus" && ('# Sales'[Invoice Date]) >= DATE(2022,10,01) && ('# Sales'[Invoice Date]) <= DATE(2022,12,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.5302),
SUM('# Sales'[Net Sales Amount])
))
refer approach
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
2 Replies
- amitchandak
Super User
LewisB , Create a measure like
x-Updated Sales Measure = Sumx('# Sales' , SWITCH(
True(),
('# Sales'[Company Code]) = "pus" && ('# Sales'[Invoice Date]) >= DATE(2023,01,01) && ('# Sales'[Invoice Date]) <= DATE(2023,03,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.4997),
('# Sales'[Company Code]) = "pus" && ('# Sales'[Invoice Date]) >= DATE(2022,10,01) && ('# Sales'[Invoice Date]) <= DATE(2022,12,31), SUMX('# Sales', '# Sales'[Net Sales Amount] * 1.5302),
SUM('# Sales'[Net Sales Amount])
))
refer approach
Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM Thanks amitchandak - this seems to have done the trick. However, the overall totals seem to be coming out wrong (it seems the totals are duplicated x the number of lines in an invoice etc). I know this will have something to do with SUMX, as that calculates line by line. See screenshot below. I'll create a new thread for this and tag you in it