Selectedvalue in a Switch measure not selecting dates correctly

[LewisB]

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])

I have made a measure as per below:

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])

)

The measure always spits out the SUM('# Sales'[Net Sales Amount]) value, no matter what date is applied (I have ensured the "pus" company is always selected).

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]

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

[amitchandak · Accepted 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