sum of previous quarter

[Anonymous]

Hi,

I have the below table and i would like it to sum the previous quarter (based on qnum). so for the top 3 rows, against qnum 1 they should be blank, or 0. the next 3 rows, against qnum 2, should all show thr sum of qnum 1, so 20. and so on.

[amitchandak]

@Anonymous , You can use time intelligence with date table and try measures like

example

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)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))
trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

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 :radacad sqlbi My Video Series Appreciate your Kudos.

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

[Anonymous]

Hi, would this work, as my quarters are not calendar year quarters. i.e jan - mar is q1.  my q1 is apr - jun.

[amitchandak]

@Anonymous , Apr to Jun it will work. if you have a date. Solution for other qtr

Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH)
Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+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 Day] <=max([Qtr Day])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day])))

You can this Qtr vs Last Qtr with Rank on YYYYQ also

[Anonymous]

i have used your last quarter forumla, but im not sure i have done it right.

u2g = CALCULATE(sum('CP Count'[Under 2]),filter(all('CP Count'),'CP Count'[Qnum]=max('CP Count'[Qnum])-1))

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

You  can  try  calculated column as below:

Column =
CALCULATE (
    SUM ( 'Table'[Under2] ),
    FILTER ( 'Table', 'Table'[Qnum] = EARLIER ( 'Table'[Qnum] ) - 1 )
)

Check attached pbix for more details.

Best Regards,
Community Support Team _ Eason