Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])
)
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?
Solved! Go to Solution.
@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
@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
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |