Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
can you please assist on how do i calculate the % change between previous and current quarters in power bi
Solved! Go to Solution.
HI @Anonymous,
You can use year and quarter functions to extract the year and quarter numbers from the date field value, then you can use them in expression to get corresponding current and previous quarter values to calculate the diff.
formual =
VAR currDate =
MAX ( Table[Date] )
VAR prevDate =
DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
VAR currQuarter =
CALCULATE (
SUM ( Table[Amount] ),
FILTER (
ALLSELECTED ( Table ),
YEAR ( Table[Date] ) = YEAR ( currDate )
&& QUARTER ( Table[Date] ) = QUARTER ( currDate )
),
VALUES ( Table[Category] )
)
VAR prevQuarter =
CALCULATE (
SUM ( Table[Amount] ),
FILTER (
ALLSELECTED ( Table ),
YEAR ( Table[Date] ) = YEAR ( prevDate )
&& QUARTER ( Table[Date] ) = QUARTER ( prevDate )
),
VALUES ( Table[Category] )
)
RETURN
DIVIDE ( currQuarter - prevQuarter, prevQuarter )
Regards,
Xiaoxin Sheng
HI @Anonymous,
You can use year and quarter functions to extract the year and quarter numbers from the date field value, then you can use them in expression to get corresponding current and previous quarter values to calculate the diff.
formual =
VAR currDate =
MAX ( Table[Date] )
VAR prevDate =
DATE ( YEAR ( currDate ), MONTH ( currDate ) - 3, DAY ( currDate ) )
VAR currQuarter =
CALCULATE (
SUM ( Table[Amount] ),
FILTER (
ALLSELECTED ( Table ),
YEAR ( Table[Date] ) = YEAR ( currDate )
&& QUARTER ( Table[Date] ) = QUARTER ( currDate )
),
VALUES ( Table[Category] )
)
VAR prevQuarter =
CALCULATE (
SUM ( Table[Amount] ),
FILTER (
ALLSELECTED ( Table ),
YEAR ( Table[Date] ) = YEAR ( prevDate )
&& QUARTER ( Table[Date] ) = QUARTER ( prevDate )
),
VALUES ( Table[Category] )
)
RETURN
DIVIDE ( currQuarter - prevQuarter, prevQuarter )
Regards,
Xiaoxin Sheng
@Anonymous read this full article:
https://www.daxpatterns.com/standard-time-related-calculations/
You also have there a section about quarter over quarter.