cancel
Showing results for
Did you mean:
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.

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

Hi,

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

Use the below link for the same.

Regards,

manikumar

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)))

//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 :

Proud to be a Super User!

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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