Percent Change Measure based on Filter

[joedim]

I have a database set up that provides the total T&E expense broken out by Area (Consulting, Education, etc.) by Quarter (2018 Q1, 2018 Q2, etc. stored as text). I am attaching an image as an example.

I have a clustered column chart to show my total expense by Area (attached) with a filter that allows the user to select which two (or more) periods to compare (in this example, comparing 2018 Q2 vs 2019 Q2).

I would like to add a line to my chart showing the % change between the two periods selected, but not sure how to build the measure since the periods analyzed will change with the filter. Example of my dataCurrent Bar Chart

[Ashish_Mathur]

Hi,

In your question you state that the user can select 2 (or more) periods.  So if the user selects 3 periods, then how do you propose to calculate the % change?

[Anonymous · Accepted solution]

@joedim - You'll need a disconnected Parameters table, like this:

Parameters = 
var periods = VALUES(YourTable[Period])
return CROSSJOIN(
    SELECTCOLUMNS(periods,"Period 1", [Period]),
    SELECTCOLUMNS(periods,"Period 2", [Period])
)

Then, you can add the measure like this:

Period 2 % of Period 1 = 
var period1 = CALCULATE(
    SUM(YourTable[Expense]),
    TREATAS(VALUES(Parameters[Period 1]),YourTable[Period])
)
var period2 = CALCULATE(
    SUM(YourTable[Expense]),
    TREATAS(VALUES(Parameters[Period 2]),YourTable[Period])
)
return DIVIDE(period2,period1)

Finally, add Period 1 and Period 2 as slicers, and the new measure to the visual.

Hope this helps,

Nathan