Forum Discussion
Calculate Percentage Difference Between Quarters
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])
to
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!
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.
2 Replies
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.
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!
