QoQ variance

[jfranklin27]

Hello, I am trying to get a QoQ variance in "Tests" based on my "Actual_Start_Date" instead of just the calendar date. I created a "TestVarByYear" Measure but to get the years seperated, I filtered only to my Audit Name that contained that year in the name.  Everything I keep finding about Quarters are how to calculate bason on the Calendar table. I am having a hard time trying to relate the Quarter to the Test date. This is how I calculated "Tests": Tests = COUNTROWS(Audit). For reference "Actual_Start_Date" also comes from the "Audit" table. 

This is what I am trying to recreate by QoQ instead:

Thank you!

[v-zhenbw-msft]

Hi @jfranklin27 ,

Sorry for that we don’t understand your issue clearly.

Do you want to count the number of rows in the Audit table for the last quarter of the selected quarter?

For example, we select the third quarter in 2020, then will it count rows in 2020 second quarter?

Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[DataZoe]

@jfranklin27 You could try this measure:

Tests Prev Quarter = calculate([Tests],PREVIOUSQUARTER(Audit[Actual_Start_Date]))

https://docs.microsoft.com/en-us/dax/previousquarter-function-dax

You can create the previous year on similiarily:

Tests Prev Year = calculate([Tests],PREVIOUSYEAR(Audit[Actual_Start_Date])

https://docs.microsoft.com/en-us/dax/previousyear-function-dax

The calendar date is referenced because usually you can create a date table, and mark it as a date table, then join it to the Actual_Start_Date (provided it's a date, not datetime). Then you would simply change out Audit[Acutal_Start_Date] with the Date[Date] column in the above measures.

= CALENDAR (MINX (Audit, [Actual_Start_Date]), MAXX (Audit, [Actual_Start_Date]))

https://docs.microsoft.com/en-us/dax/calendar-function-dax

[amitchandak]

@jfranklin27 , not very cleat you can use time intelligence and date table

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

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

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.