cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

KPI goal set to previous quarter

Hi,

I have some KPIs and I would like to set the goal to be the previous quarter.  Our quarters run from April to March, so unless I'm mistaken, I cant use the previousquarter formula.

My data is in a table with multiple columns as below.

Rickfear_0-1601299611225.png

so for example, for HospAdSH i want it to show 141 for 2020 Q1 (it does this), and 234 as the goal (the Q4 2019 sum)

3 REPLIES 3
Solution Specialist
Solution Specialist

Hi, 

You can create a calculated column to have your Quartere starting from April. 

 

Use the below link for the same. 

 

https://radacad.com/some-methods-for-calculating-quarter-in-dax-for-power-bi

 

Regards, 

manikumar

Super User IV
Super User IV

@Rickfear , You can use datesqtd. It just looks for qtr. You can use that Ir your Qtr start fron Jan, Apr, Jul and Oct

examples

// With TI

 

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

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

 

 

//Without TI

 

Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"3/31"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
Qtr Month No = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],MONTH)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)	
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Month No ] <=max([Qtr Month No ])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Month No ] <=max([Qtr Month No ])))

 

 

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions


Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helper II
Helper II

Thanks both, I ended up going in a different direction.  I added a column to the end called Qnum which goes 1,2,2,2,3,3,3,4,4,4 etc.  I then added additonal goal columns with the formula =SUMIF($Q:$Q,[@Qnum]-1,C:C).  This now gives me my required goals to use.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors