Skip to main content
cancel
Showing results for 
Search instead 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

Reply
icsanelli00
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
rajendraongole1
Community Champion
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!
Appreciate your Kudos!!

View solution in original post

2 REPLIES 2
icsanelli00
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

rajendraongole1
Community Champion
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!
Appreciate your Kudos!!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.