Calculate the Percent Change of Expense Amount based on any Fiscal quarter selected from the slicer

[kalkhudary]

Hello everyone, 

I am trying to Dax the percent change of the expense amount from fiscal year quarter based on selected FY_Quarter from slicer in a way that the Dax is dynamic based on selected options in the slicer.

The below matrix table shows the sum values of the expense based on Quarter 1 and 2 for FY22, FY23, FY24 only (see slicer selection). I want to be able to calculate the percent change and add it as column between FY23 and FY24 to show percent change from the value of Airefare expense in FY23 (3.9M) to the value in FY22 (0.2M) and the same for FY23 and FY24 in a way that it is dynamic based on selected year quarter from the slicer.

I know that I need to use Min and Max but not able to know what to include to make sure that the dax is dynamic base don slicer selection.

Any help would be appreciated.

[danextian]

hi @kalkhudary ,

What you are trying to achieve, while not impossible, is rather finnicky. Getting the difference  is one thing. Adding  extra columns to the matrix is another thing. Power BI doesn't work like Excel does. Also, if I were to do it, I would just use a separate slicer for the quarter for the difference and another one for the FYs I'd want to appear in the matrix. The approach usually involves a disconnected table with rows as placeholders for columns you want to appear in the matrix then you use a measure/measures to make it appear the value you want based on the current row value. Attached is a sample of such an approach. Notice that next to the tickets is the name of month.

[kalkhudary]

@danextian Thanks for providing your thoughts towards solving this topic. I somehow understood your approach but not sure how to apply it on my above data based on the example your provided. It was a bit confusing. Any one you can use my data to demonstrate it. Would be thankful.

[danextian]

As always post a workable data (not an image). Or post a link to a sanitized copy of your pbix.

[bhelou]

try this , 

PercentChange =
VAR CurrentExpense = [ExpenseAmount]
VAR PreviousExpense =
CALCULATE (
[ExpenseAmount],
FILTER (
ALLSELECTED ( 'YourTable'[FY_Quarter] ),
'YourTable'[FY_Quarter] = MIN ( 'YourTable'[FY_Quarter] ) - 1
)
)
RETURN
IF (
ISBLANK ( PreviousExpense ),
BLANK (),
DIVIDE ( CurrentExpense - PreviousExpense, PreviousExpense )
)

[kalkhudary]

@bhelou I have tried the above DAx but once you add it to the matrix table, the table disappear. Probably related to how the dax is interacting with the slicer or filters applied. what do you think? A bit tricky.