cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
princesharma
Regular Visitor

How to create Quarter-on-Quarter % Change in Direct Query mode in Power BI?

 

I want to create Quarter-on-Quarter % Change in Direct Query mode in Power BI

Creating measures like Quarter on Quarter percentage is quite easy is Import mode in power BI. But if we try them in Direct Query mode, the time intelligence measures are not available.

I even tried the DAX generated for Quarter-on-Quarter % Change in Import mode(as shown below) to do the same in Direct Query mode. But Time intelligence functions does not work in Direct Query mode.

 

    Count of IssueId QoQ% = 
IF(
    ISFILTERED('services_user stat_view'[CreatedDate]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_QUARTER =
        CALCULATE(
            COUNTA('services_user stat_view'[IssueId]),
            DATEADD(
                'services_user stat_view'[CreatedDate].[Date],
                -1,
                QUARTER
            )
        )
    RETURN
        DIVIDE(
            COUNTA('services_user stat_view'[IssueId])
                - __PREV_QUARTER,
            __PREV_QUARTER
        )
)

Could you please help?

1 REPLY 1
v-lili6-msft
Community Support
Community Support

HI, @princesharma

The time intelligence takes a toll when connecting to direct query. 

If you are expecting the date hierarchy in specific , you would have to create it for yourself by making columns [year], [quarter],[month],[date] and then use them to create a hierarchy.

 

You can get the modelling and reporting limitaions for direct query connection in 

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

 

and for your  Quarter-on-Quarter % measure, you could try this way

Step1:

Add a date table with this columns  [year], [quarter], [year quarter] and a rank column for [year quarter] in the data source

Step2:

After get data then create a relationship between services_user stat_view with date table

Step3:

Use this formula to create a Quarter-on-Quarter % measure

Measure = 
IF(
	ISFILTERED('Date'[YQRANK]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __PREV_QUARTER = CALCULATE(SUM('services_user stat_view'[Qty]), FILTER(ALL('Date'),MAX('Date'[YQRANK])='Date'[YQRANK]+1))
	RETURN
		DIVIDE(SUM('services_user stat_view'[Qty]) - __PREV_QUARTER, __PREV_QUARTER)
)

Note:

Why do we need to add a column and a rank column for [year quarter] ?

for example:

This is just to make it easy to calculate Quarter-on-Quarter % Change for year 2018 Q4 and year 2019 Q1

 

and here is pbix, you could refer to it.but all the date column is done in the data source.

 

Best Regards,

Lin

 

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.