Compare a table values based on two slicers

[mb0307]

Hi,

I have one table:

WEEK NUMBER	SALES
22	1
23	100
24	3
25	500

I want a user to be able to select any two weeks (only two not more than that) from the same table and substract the sales values from two selected weeks. 

So if I create 

OR

If Min and Max of the selected values (not in between range) can be used for substracting Max from Min selected week. 

for both of the above examples, Week 25 (max week) - Week 23 (Min week) = 400

Thanks

[mahoneypat]

You can make two What If Parameters to be used as your two week slicers.  Assuming you are a Date table with a WeekNumber column, you can then make a measure like this:

Selected Weeks Difference =
VAR __firstweek =
    CALCULATE (
        [Total Sales],
        'Date'[WeekNumber] = SELECTEDVALUE ( Slicer1[Week] )
    )
VAR __secondweek =
    CALCULATE (
        [Total Sales],
        'Date'[WeekNumber] = SELECTEDVALUE ( Slicer2[Week] )
    )
RETURN
    __secondweek - __firstweek

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mb0307]

@mahoneypat   Thanks for sending the solution but it shows the following error:

A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[mb0307]

Managed to solve the issue by giving Week Number from Sales table not Date table.

If you how to correct the error previously mentioned then i would prefer to use the Date table's Week Number.  My Date table has a 1:Many relation with the Sales table.

Also, can we use SLIDER's (min & max) as explained in the first post to get the same result from one SLIDER?

[mahoneypat · Accepted solution]

Sorry for the error.  I should have first stored the SELECTEDVALUES()s as variable, and then used the variables in the CALCULATE expressions.

Also, here is how to do it with a single slicer.  It assumes you've make a Weeks table using Weeks = VALUES('Date'[Weeknumber)

Because you have a relationship between the Date table and your other table, I added in an ALL into each calculate.  If it removes too many filters, you can back it off to ALLSELECTED or just ALL('Date'[Weeknumber]), as I'm not sure what else you have in your report page or visual.

Selected Weeks Difference =
VAR minweek =
    MIN ( Weeks[WeekNumber] )
VAR maxweek =
    MAX ( Weeks[WeekNumber] )
VAR __firstweek =
    CALCULATE ( [Total Sales], ALL ( 'Date' ), 'Date'[WeekNumber] = minweek )
VAR __secondweek =
    CALCULATE ( [Total Sales], ALL ( 'Date' ), 'Date'[WeekNumber] = maxweek )
RETURN
    __secondweek - __firstweek

You can use the same pattern as above if you stay with the SELECTEDVALUE route.  Just put the two SELECTEDVALUE clauses into the variables instead of Min/Max.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat