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
magnifybi
Resolver I
Resolver I

Getting (Blank) on KPI "Goal" when looking at Current Financial Year vs Previous

I have a KPI that I want to show the current Financial Year Sales as the Indicator and the Previous as the Goal, I have the following formulas which work on their own in a stand alone visual;

 

Gross Revenue ThisYear = CALCULATE(SUM('General Ledger Report'[Amount]),FILTER('Chart of Accounts', 'Chart of Accounts'[*Type] = "Revenue"),FILTER(Dates, Dates[Curr/Prev Fin Year] = "Current" ))

 

Gross Revenue LastYear = CALCULATE(SUM('General Ledger Report'[Amount]),FILTER('Chart of Accounts', 'Chart of Accounts'[*Type] = "Revenue"),FILTER(Dates, Dates[Curr/Prev Fin Year] = "Previous" ))
 
"Curr/Prev Fin Year" here is a calculated column marking the financial year running from July-June as the current or previous.
 
Problem I'm getting is the Gross Revenue LastYear is showing as Blank when I put it in the "Goal", ThisYear is showing correctly.
 
I assume this is to do with the "Trend Axis" but I've tried lots of different things and can't get this to display correctly.
 
Any help would be great.
 
Thanks
 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@magnifybi , if you have date then you can use time intelligence, if not you can use a separate year(date)  table

 

examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"6/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"6/30"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"6/30"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"6/30"))

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@magnifybi , if you have date then you can use time intelligence, if not you can use a separate year(date)  table

 

examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"6/30"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"6/30"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"6/30"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"6/30"))

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

 

Thanks for this, but the YTD calculations are not what I need, I need an overall sum, so that I can choose a month name and a result comes back for the month in the current year and the same month in the previous year

Helpful resources

Announcements
Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.

Top Solution Authors