topic Re: QoQ calculation based on Quarter selection in slicer in DAX Commands and Tips

QoQ calculation based on Quarter selection in slicer

Jane689 — Thu, 17 Nov 2022 09:25:54 GMT

Hi,

i have a table which has columns visit and fyqtr.

Now i want to show QoQ change in visits based on fyqtr selection in slicer .

so if i select FY23Q1 in slicer then QoQ ((FY23Q1-FY22Q4)/FY22Q4) change in visit should be displayed

Re: QoQ calculation based on Quarter selection in slicer

Uspace87 — Thu, 17 Nov 2022 11:30:50 GMT

Hi,

What I would do id the follow:

1) Create a Calculated Colum to use as a FY_QRT filter:

FY_QRT Filter = SELECTCOLUMNS(Visits,"FY_Qrt",Visits[FY_QRT])

2) Create a measure that show the number of visits based on the Year_Qrt you select on the Calculated column:

Visits Selected Qrt =

VAR QRT =

SELECTEDVALUE ( 'FY_QRT Filter'[FY_Qrt])

RETURN

CALCULATE (

SUM (Visits[Visits]),FILTER(ALLSELECTED(Visits),

Visits[FY_QRT] = QRT )

)

3) Create a measure that calculate the difference between the all the Quarters and the Selected quarter:

QoQ = [Visits Selected Qrt]-SUM(Visits[Visits])

the Result will be a table like this:

Re: QoQ calculation based on Quarter selection in slicer

Jane689 — Thu, 17 Nov 2022 11:50:46 GMT

Uspace87 Thanks for the solution...

i want QoQ difference between selected quarter and previous quarter not all quarters

Re: QoQ calculation based on Quarter selection in slicer

Uspace87 — Thu, 17 Nov 2022 12:13:56 GMT

Hi,

The Best Practice would be to Create a "Date Table" and connect it to your table using the "Quarter-year" and use time intelligence functions in DAX

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Then you can Create two measure:

Visits QTD = CALCULATE( Visits[Visists], DATESQTD(Date_Table[Date]))
2)

Visists Previous Quarter = CALCULATE(

Visits[Visits QTD], DATEADD(Date_Table[Date],-1,QUARTER))

Then you can use the "Quarter-Year" Column in your Calendar table to filter your table:

Re: QoQ calculation based on Quarter selection in slicer

Jane689 — Thu, 17 Nov 2022 14:42:48 GMT

Uspace87 could you please share your pbix file !!

Thanks in advance ..

Re: QoQ calculation based on Quarter selection in slicer

Uspace87 — Thu, 17 Nov 2022 15:47:31 GMT

Hi,

You can download it from github

https://github.com/uspace87/Power-Bi/blob/c120c53b3d604bd5f7626e86a8bdab66e2f066bd/Visits%20QoQ.pbix

Re: QoQ calculation based on Quarter selection in slicer

Jane689 — Fri, 18 Nov 2022 11:31:43 GMT

Uspace87 i tried above soltuion but am getting blank values for visit_prev_qtr .

only visits qtd is populated correctly .

Also , fyqtr column has duplicate values

Re: QoQ calculation based on Quarter selection in slicer

Uspace87 — Fri, 18 Nov 2022 12:12:14 GMT

Hi Jane,

Mine is just and example, you would get blank values in "visit_prev_qtr" if there aren't data for previous Qrt and that depends on your data.

Can you provide an example?

Thank you.

Re: QoQ calculation based on Quarter selection in slicer

Jane689 — Fri, 18 Nov 2022 12:32:20 GMT

I tried every quarter from slicer but its giving blank values always

Re: QoQ calculation based on Quarter selection in slicer

Uspace87 — Fri, 18 Nov 2022 12:44:59 GMT

Have you created the connection between date table and fact table like below: