Creating a measure dynamically based on a slicer value

[MNGoodyear]

I have a set of sales data and this data is summarised by account , year and volume

I can create a measure to calculate my 22 sales volume  "22 Sales Volume"

I then want to have an additional measure showing the sales volume for another year based on the slicer value the user selects  - "Selected Year Volume"

I would then like to create another measure which calculates the difference between 22 and the value for the selected year giving a gap analysis between the sales for 22 and the total for the selected year - "22 Sales Volume" - "Selected Year Volume"

For example if i select year 20 then it would look like below

i have tried using the hasonefilter command but cannot get it to sum the qty for that year basius the selcted value

account	year	sales				22	selected year 20	gap
a	22	100			a	100	300	-200
a	21	200			b	0	900	-900
a	20	300
a	19	500
a	18	600
b	22	0
b	21	500
b	20	900
b	19	500
b	18	600

[Anonymous · Accepted solution]

Hi @MNGoodyear ,

You need a seperate year table for the sales of the selected year. Here's the solution.

Raw data is as follows which you provided.

1.Create a table using dax. There's no relationship between tables.

2.Create a measure for the sales of the selected year from the year table.

selected year =
CALCULATE (
    SUM ( 'Table'[sales] ),
    FILTER (
        ALL ( 'Table' ),
        [account] = MAX ( 'Table'[account] )
            && [year] = SELECTEDVALUE ( 'Year'[year] )
    )
)

And the gap measure is below.

gap = SUM('Table'[sales])-[selected year]

3.I made arrows indicating the source of the slicer field and which value was generated. 

You can download my attachment for more details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.