looking for advice - percentage change

[Anonymous]

dear all,

need some advice here, i have this file

is it possible using this file to get the percentage change, is so what are the possible steps?

Thanks so much in advance

[dax · Accepted solution]

Hi mellyVK,

I create a column like below(you could refer to my  upload sample)

FinancialYearNo = 
IF (
AND (
'DATA1'[Date] >= DATE ( 2015, 4, 1 ),
'DATA1'[Date] <= DATE ( 2016, 3, 31 )
),
15, 

IF (
AND (
'DATA1'[Date] >= DATE ( 2016, 4, 1 ),
'DATA1'[Date] <= DATE ( 2017, 3, 31 )
),
16, 

IF (
AND (
'DATA1'[Date] >= DATE ( 2017, 4, 1 ),
'DATA1'[Date] <= DATE ( 2018, 3, 31 )
),
17, 

14)
))

Then create measures like below

current = CALCULATE(SUM(DATA1[Value]), ALLEXCEPT(DATA1,DATA1[Product],DATA1[FinancialYear]))
pre = CALCULATE(SUM(DATA1[Value]),FILTER( ALLEXCEPT(DATA1,DATA1[Product]) , DATA1[FinancialYearNo]=MIN(DATA1[FinancialYearNo])-1)) 
% change = if(ISBLANK([pre])|| [pre]=0 ,0,([current]-[pre])/[pre])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous % change of what? Value? change over month by each product? You post is not very clear. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@parry2k 

This is a sale data report, thus referring to the percentage change of total sale compared to the previous FY.
in my case, the fiscal year start Apr. is it possible?
Can anyone advice?

[dax · Accepted solution]

Hi mellyVK,

I create a column like below(you could refer to my  upload sample)

FinancialYearNo = 
IF (
AND (
'DATA1'[Date] >= DATE ( 2015, 4, 1 ),
'DATA1'[Date] <= DATE ( 2016, 3, 31 )
),
15, 

IF (
AND (
'DATA1'[Date] >= DATE ( 2016, 4, 1 ),
'DATA1'[Date] <= DATE ( 2017, 3, 31 )
),
16, 

IF (
AND (
'DATA1'[Date] >= DATE ( 2017, 4, 1 ),
'DATA1'[Date] <= DATE ( 2018, 3, 31 )
),
17, 

14)
))

Then create measures like below

current = CALCULATE(SUM(DATA1[Value]), ALLEXCEPT(DATA1,DATA1[Product],DATA1[FinancialYear]))
pre = CALCULATE(SUM(DATA1[Value]),FILTER( ALLEXCEPT(DATA1,DATA1[Product]) , DATA1[FinancialYearNo]=MIN(DATA1[FinancialYearNo])-1)) 
% change = if(ISBLANK([pre])|| [pre]=0 ,0,([current]-[pre])/[pre])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@dax 

Thanks so much!

by the way how about the % sign on the chart?

[dax · Accepted solution]

Hi mellyVK,

You could set format like below for %change

In addition, for thr measures

current = CALCULATE(SUM(DATA1[Value]), ALLEXCEPT(DATA1,DATA1[Product],DATA1[FinancialYear])) 
// this calculate total based on product and year group
pre = CALCULATE(SUM(DATA1[Value]),FILTER( ALLEXCEPT(DATA1,DATA1[Product]) , DATA1[FinancialYearNo]=MIN(DATA1[FinancialYearNo])-1)) 
//  DATA1[FinancialYearNo]=MIN(DATA1[FinancialYearNo])-1) filter year is previous year of current row, then calculate total
% change = if(ISBLANK([pre])|| [pre]=0 ,0,([current]-[pre])/[pre])

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@dax 

hi, i am also trying to get it to compare previous fiscal Quarter over this fiscal Quarter.

I followed this video & got the quarter column

I adjusted the above forumla to QoQ, got it working for current QoQ,

but not able to display chart for previous qoq and %change.

can anyone advice?

Thank you.

[Ashish_Mathur]

Hi,

For the Previous Quarter, try this measure

=CALCULATE([your measure],PREVIOUSQUARTER(Calendar[Date]))