Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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:
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.
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?
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.
@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.
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)
Thanks, i got them working...
for %change, i tried following but dont' work. how should it be?
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
Need to close all('Calendar'). ")" missing
@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.
Moved from today to selection and also changed to fiscal year
https://www.dropbox.com/s/6cn8ksb690i4cjg/Maryland.pbix?dl=0
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.
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)))
Share the formula or the file.
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.
unable to click on link
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |