cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## period difference by year and quarter

hello all
i'm trying to build a financial statement in a matrix using measure to calculate the variation between periods
i used this formula and it works fine if i look at the matrix years by years
CALCULATE([Balance],account_account[n° mastro]="xx",SAMEPERIODLASTYEAR('Calendar'[Data].[Date]))-CALCULATE([Balance],account_account[n° mastro]="xx")

but if i expand the date column to quartes of course doesn't work

so i used this measure to se the variation quarter by quartes
CALCULATE([Balance],account_account[n° mastro]="xx",ENDOFMONTH(PREVIOUSQUARTER('Calendar'[Data].[Date])))-CALCULATE([Balance],account_account[n° mastro]="xx",ENDOFQUARTER('Calendar'[Data].[Date]))

any idea how can i do a measure that work for both visualization, by years and by quarters

thanks
1 ACCEPTED SOLUTION
Community Champion

Hi @icsanelli00 - Try the below measure for by years and by quarters with variation.

BalanceVariation =
VAR CurrentBalance = CALCULATE([Balance], account_account[n° mastro] = "xx")
VAR PreviousBalanceYear =
CALCULATE(
[Balance],
account_account[n° mastro] = "xx",
SAMEPERIODLASTYEAR('Calendar'[Data].[Date])
)
VAR PreviousBalanceQuarter =
CALCULATE(
[Balance],
account_account[n° mastro] = "xx",
PREVIOUSQUARTER('Calendar'[Data].[Date])
)
RETURN
IF(
ISINSCOPE('Calendar'[Data].[Year]),
PreviousBalanceYear - CurrentBalance,
IF(
ISINSCOPE('Calendar'[Data].[Quarter]),
PreviousBalanceQuarter - CurrentBalance,
BLANK() -- Handle other contexts if necessary
)
)

It works on date hierarchy at different levels measure works correctly

Did I answer your question? Mark my post as a solution! This will help others on the forum!

2 REPLIES 2
Regular Visitor

thanks, my problem now is that i need for the first quarter to calculate the difference between the first quarter and the value of the entire previus years,.

i tried many measure but the problem is that when i look at the matrix visual by quarter the calculation of the entire previus year doesn't work

Community Champion

Hi @icsanelli00 - Try the below measure for by years and by quarters with variation.

BalanceVariation =
VAR CurrentBalance = CALCULATE([Balance], account_account[n° mastro] = "xx")
VAR PreviousBalanceYear =
CALCULATE(
[Balance],
account_account[n° mastro] = "xx",
SAMEPERIODLASTYEAR('Calendar'[Data].[Date])
)
VAR PreviousBalanceQuarter =
CALCULATE(
[Balance],
account_account[n° mastro] = "xx",
PREVIOUSQUARTER('Calendar'[Data].[Date])
)
RETURN
IF(
ISINSCOPE('Calendar'[Data].[Year]),
PreviousBalanceYear - CurrentBalance,
IF(
ISINSCOPE('Calendar'[Data].[Quarter]),
PreviousBalanceQuarter - CurrentBalance,
BLANK() -- Handle other contexts if necessary
)
)

It works on date hierarchy at different levels measure works correctly

Did I answer your question? Mark my post as a solution! This will help others on the forum!