topic Re: Calculate Percentage Difference Between Quarters in DAX Commands and Tips

Calculate Percentage Difference Between Quarters

lisamb1 — Tue, 13 Jul 2021 20:06:16 GMT

HI All, I am trying to calculate the percentage cost difference between 2 quarters. I am able to calculater a percentage costs difference between 2 months, but the quarters I cannot seem to find a solution. I have a table that includes the following fields Dates, EBSCostAvoidance column with costs that will be used to calculate the cost difference between the dates for the quarter percentage difference. I also have a QTR and Year column in this same table that I added trying to get the quarter percentages to work. I need for the percentage to display in a card visualization so that I can use unichar arrows for the up and down percentages. I do not need help with the unichar arrows as I have done this with the percentage changes for 2 months.

I also have a Calendar table that has a date column, QTR and Year Column.

Example table with data for cost percentage calculation:

Example Calendar table data:

Code that I have for Quarter percentages:

CustomAvoidanceTotalForPreviousQuarter =
CALCULATE(
SUM(EBSOptimization[CostAvoidance])
, PREVIOUSQUARTER(EBSOptimization[Dates])
)

--- I have also changed the code above to look at the Calendar Dates field PREVIOUSQUARTER(EBSOptimization[Dates])

PREVIOUSQUARTER(Calendar[Date])

thinking it may help with locating the quarter. but that did not resolve the issue.

CustomAvoidanceCostDifferenceQTR =
VAR CurrentQTRCosts = SUM(EBSOptimization[CostAvoidance])
VAR PreviousQTRCosts = [CustomAvoidanceTotalForPreviousQuarter]
VAR Result = CurrentQTRCosts - PreviousQTRCosts
RETURN
Result

CustomPercentageOfChangeQTR =
DIVIDE([CustomAvoidanceCostDifferenceQTR],
[CustomAvoidanceTotalForPreviousQuarter]
)

Does anyone have any ideas on how I can figure percentage change between 2 quarters?

Thank you!

Re: Calculate Percentage Difference Between Quarters

Anonymous — Wed, 14 Jul 2021 09:26:26 GMT

A lot of text.... but where's the problem? 'Cause I can't see anything clearly pointing out the issue.

This piece of code:

VAR CurrentQTRCosts = SUM(EBSOptimization[CostAvoidance])

will only sum up values that are visible in the current context. It will not auto-magically give you a quarter's value. So, if you select a period of time from your Calendar that happens to be a full quarter, the measures will work correctly. If you select something different, they won't.

Is this the problem here?

On top of that, time-intel functions work correctly when you use them on a proper Calendar, not when you want to use them with any column in any table.

Re: Calculate Percentage Difference Between Quarters

lisamb1 — Wed, 14 Jul 2021 12:46:38 GMT

daxer thank you for your assistance I am still learning Power BI and this did resolve the issue. I thought the CurrentQTRCosts and PreviousQTRCosts would identify the quarters, but after your explanation I did go pick a full quarter and that did resolve the issue of the percentage not displaying correctly.

Per your suggestion I will change the code to look at my Calendar table instead of the date in my data table. I was not aware of time-intel functions not working correctly if I did not use the Calendar table.

Thank you again!