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.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors