calculating last year using fiscal year number

[Anonymous]

hi all...

i am trying to calculate last year using the formula..but when i added a calendar table and added the fiscalyearno, it dont work. any advice.?

 lastyear = CALCULATE(SUM(Sheet1[Value]),FILTER(ALLEXCEPT(Sheet1,Sheet1[ProductName]),sheet1[fiscalyearno]=min(sheet1[fiscalyearno]-1)

thank you.

[v-yingjl]

Hi @Anonymous ,

I have modified your percentchange measure  to solve your problems, here it is:

percentchange = if(ISBLANK([lastyear])|| [lastyear]=0 ,0,(CALCULATE(SUM(Sheet1[Value])-[lastyear])/[lastyear]))

You will get the result like this:

And here is the demo , please try it:

PBIX 

Best Regards,

Yingjie Li

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

[Anonymous]

@v-yingjl @amitchandak 

So it mean if i have calendar table with all the fiscal year, monthsort, quarter. I am not able to use them in my data calculation?

[amitchandak]

You can use them. You need to make sure you use all(Calendar) , so that the filter gets removed and you can apply your logic.

[Anonymous]

@amitchandak 
would you mind explaining a bit more on it.
my 2 tables are already linked ie...calendar date(start 2010) in date table & my data table date.

what else i need to do?

Thank you so much.

[amitchandak]

Refer These examples

Sales Last Year = CALCULATE(sum(Sales[Sales]),all(calendar),Calendar[Year]= ( Year(NOW())-1))


Sales Fin Year = 
Var _fin_year = maxx(filter(Calendar,Calendar[date]=today()),Calendar[finYear])
Return
CALCULATE(sum(Sales[Sales]),all(Calendar),Calendar[finYear]= _fin_year)

Sales Fin last Year = 
Var _fin_year = maxx(filter(Calendar,Calendar[date]=today()),Calendar[finYear]) -1
Return
CALCULATE(sum(Sales[Sales]),all(Calendar),Calendar[finYear]= _fin_year)

[Anonymous]

@amitchandak 

Thanks, i got them working...
for %change, i tried following but dont' work. how should it be?

%sales = ([Sales Fin Year]-[Sales last Fin Year])/[Sales last Fin Year])

by the way, if the forumla work, does it also mean i can't use my calendar date as filters?

[amitchandak]

The use of the calendar will fix the dates. Else you will get those of all dates

The Change % should have work

or try

%sales = divide([Sales last Fin Year],[Sales last Fin Year]) -1

[Anonymous]

@amitchandak 

would you advice on this error?

Thanks so much

[amitchandak]

Need to close all('Calendar'). ")" missing

[Anonymous]

@amitchandak 
got the formula working.
However the figures seem to be wrong?
Finyear & last finyear figure are the always the same?

Also when i linked the table to calendar, the figures are all gone.

Any advice. Thank you so much for helping me.

[amitchandak]

Moved from today to selection and also changed to fiscal year

https://www.dropbox.com/s/6cn8ksb690i4cjg/Maryland.pbix?dl=0

[Anonymous]

@amitchandak 

thank you so much..i finally made it.

1 more question, for calculating quarter change, i am using the similar forumla with 'Fiscal Month Sort Order'. But the value are wrong. could you advice me on the formula.

thanks once again.

[amitchandak]

Your Qtr seems to have jan, feb , march and so on. The only diff is qtr no.

Check if these time intelligence function can work for you

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

[Anonymous]

@amitchandak 

hmmm..there are no errors in formula but got this in the visual. Any idea?

[amitchandak]

Share the formula or the file.

[Anonymous]

@amitchandak 

i am using the forumla shared by you earlier.

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

Here the  file.

[amitchandak]

unable to click on link

[Anonymous]

opps, sorry...https://gofile.io/?c=BKDYW3

@amitchandak 

[amitchandak]

You Calendar table has some issue. When I tried making it a date table, it gives an error for gap in date range.

I created another Calendar table(Not maked as date) and calculated prior qtr and it worked out.

https://www.dropbox.com/s/kvdtmj1ivw1hy1f/Maryland_quarter.pbix?dl=0

[amitchandak]

I did some changes. Did not validated the data. Also taken max date. In the case of a slicer, the logic needs to be changed.

lastyear = 
var _max = maxx(Sheet1,Sheet1[Date])
var _maxYear =maxx(filter(mydateMain,mydateMain[Calendar Date]=_max),mydateMain[Fiscal Year])-1
return
CALCULATE(SUM(Sheet1[Value]),ALLEXCEPT(Sheet1,Sheet1[ProductName]),FILTER(all(mydateMain),mydateMain[Fiscal Year]=_maxYear))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin