How to calculate fiscal QoQ and YoY ?

[saipawar]

Hi,

I have a field in the datset as FISCAL_QUARTER with data as FY19Q1,FY19Q2.....FY20Q3,FY20Q4,

calculated field as FREE_SIGNUPS that gives # of signups in a given period of time.

I'd like to have a visual similar to what I've created for rolling 28 days using a KPI card

 

For a QoQ view, I'd like to know how can I capture the previous quarter's signups and thus have it in a rolling fashion(if possible).

Finally, I'd always want the KPI card to view the current quarter's data, followed by previous quarter and the %growth.

Here's how the table view looks like 

fiscal_quarter	Orders
10000	FY20Q1
9000	FY20Q2
20000	FY20Q3

Few more attributes available - 

1. 

2. 

Thanks

[Terrance]

Typically, the comparison is between reports from one quarter of the company's fiscal year with the reports from the previous quarter. Q/Q is calculated as follows: (Current quarter - previous quarter) / previous quarter.

YoY = (This Year – Last Year) / Last Year

Essentially, you are subtracting last year's number from this year's, and then dividing that by last year's number. This formula will give you the YoY number for the data set you're working with.

[amitchandak]

You need to create a date table and join it with other tables. Create Fiscal year and qtr there. As long as Qtr are Jan-Mar, Apr-jun.. these time intelligence formula will work. For YTD you can change end date. As of now it 12/31. means year will end on 12/31 to take start date based on that. You can use 3/31, 6/30 etc

Example of time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

 

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



YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Check how I created Apr-Mar calendar : https://www.dropbox.com/s/wrcyk5j66corvjg/Apr2Mar-Cal.pbix?dl=0

In case you do not have a standard Qtr. let say you have Aug-Jul year. In that case, create Qtr year in the calendar and create a rank and use that for QTD, Last QTD. This file is for Week. But will work for other non-standard periods

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[saipawar]

@amitchandak 

Since ours is not a calendar fiscal year, I am referring to the last link you shared in your reply. 

Here's what I created - 

Quarter Rank = RANKX(ALL('Calendar'),'Calendar'[FISCAL_QUARTER_START_DATE],,ASC,Dense)

When I test this measure in a table view, I cannot view any data though.

Am I missing something here?

[v-lionel-msft]

Hi @saipawar ,

Try to create the two measures.

Measure = MAX( 'Calendar'[FISCAL_QUARTER_START_DATE] )

Quarter Rank = RANKX(ALL('Calendar'),[Measure],,ASC,Dense)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saipawar]

Hi @v-lionel-msft 

Thanks for your response. I was able to create the two measures. Also looks like Measure gives me the maximum value in the dataset which is 4/29/2040 🙂 Any way I can make it current ? The current quarter start date is 01/26/2020.

Measure = MAX( 'Calendar'[FISCAL_QUARTER_START_DATE] )

Also, how about this calculation? 

Orders in last quarter = CALCULATE([Free Signups], DATESINPERIOD(CALENDAR[FISCAL_QUARTER_START_DATE], (CALENDAR[Measure])-1, -1,DAY))

Assuming "Measure" gives us current quarter date somehow.

Thanks

[v-lionel-msft]

Hi @saipawar ,

Has your problem been solved?

"Also looks like Measure gives me the maximum value in the dataset which is 4/29/2040 Any way I can make it current ? "

Is there a problem with your data type? You check the data type of the column.

"Orders in last quarter = CALCULATE([Free Signups], DATESINPERIOD(CALENDAR[FISCAL_QUARTER_START_DATE], (CALENDAR[Measure])-1, -1,DAY))"

There is no problem logically, but I am not sure whether it can return the correct value.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saipawar]

@v-lionel-msft  thanks for checking on me. The problem has not yet been solved.

I am guessing the "Quarter rank" function need to be a column and currently since the dataset I am having permissions only to create measures. 

Is there a workaround to use MAX functions with measures?

Thanks

[amitchandak]

@saipawar ,Can you share sample data and sample output.

[saipawar]

Hi @amitchandak 

I got through the Quarter Rank functions but not able to retrieve the value associated with max rank. PLease check the table below - 

Here's my calculation for "this quarter" 

Meetings this qtr = CALCULATE(SUM(DailyMtgUsage[NUMBER_OF_MEETINGS]),FILTER(all('Date'),'Date'[Max Quarter Rank]=max('Date'[Max Quarter Rank])))

DESIRED VALUE for the calculation above = 51.7 M . Currently, no value is shown in the visual.

Appreciate your help in troubleshooting this.

Thanks.

[amitchandak]

Qtr Rank, Need to be column nor measure. Try as a measure