Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

'Excess' Data when using YoY and QoQ measures

Hello,

 

I am trying to get some analytical output for my report using QoQ and YoY measures in Power BI. But when put into a figure it displays data I do not need to show. My question is then, how can I limit the 'output' of my measures to fit what I need?

 

QoQ Measure:

Volume_In QoQ_Nominal = 
IF(
	ISFILTERED('Date'[Date]),
	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('DATA'[Volume_In]),
			DATEADD('Date'[Date].[Date], -1, QUARTER)
		)
	RETURN
		__PREV_QUARTER
)

YoY Measure:

Volume_In YoY_Nominal = 
IF(
	ISFILTERED('Date'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __PREV_YEAR =
		CALCULATE(
			SUM('DATA'[Volume_In]),
			DATEADD('Date'[Date].[Date], -4, QUARTER)
		)
	RETURN
		__PREV_YEAR
)

 

Period_SlicerPeriod_Slicer

!To be noted: The Data I work with is within the period Q1 2019 to Q2 2021, and I am using a slicer to define the quarter I want to see, which can be seen to the right.

 

Now, the problem is, that too much (and too little) data is shown in my figure (below):

Figure_OutputFigure_Output

1: The Chart is filtered using the beforementioned slicer. Though I would like it to show the QoQ and YoY values as they are available and will strengthen my output (this also applies for YoY values for all 2020 quarters in the figure). How can this be done?

 

2: I am only working with 'current' data in the sense that I only want to show actuals data from full quarters. When I choose to show data up until Q2 2021 in my slicer, I do not want the figure to show the YoY measure for Q3 and Q4. How can this be done?

 

I hope I have explained my problem well enough for you guys to understand!

 

Kind Regards

1 ACCEPTED SOLUTION
Anonymous
Not applicable

You are experiencing the countless problems which stem from the fact that you're using the auto-generated date/time hierarchies. If you get rid of them and create a proper Date dimension, your troubles will be gone. Please try to forget completely that such functionality exists in PBI and always use your own calendars and write your own measures.

 

Also, you always have to be careful which tables' columns you put in which places. It's not all the same. IT does matter.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

You are experiencing the countless problems which stem from the fact that you're using the auto-generated date/time hierarchies. If you get rid of them and create a proper Date dimension, your troubles will be gone. Please try to forget completely that such functionality exists in PBI and always use your own calendars and write your own measures.

 

Also, you always have to be careful which tables' columns you put in which places. It's not all the same. IT does matter.

Anonymous
Not applicable

How do you suggest I go about making my own date dimension, so that it is proper? I am working with a rather large data set where all dates for the past two years (atleast) is present, which is why I have found the auto-generated hierachie convenient, although I might see it may be more inconvenient.

 

Also I do not really understand the last note in relation to the post, maybe you would be so kind to go a little more into depth?

 

Best regards

Anonymous
Not applicable

@Anonymous 

 

Please read carefully the documentation on any of the time-intel functions and you'll know why you need a proper Date dimension and what it should look like. For instance, https://dax.guide/dateadd. And yes, it absolutely does matter which column from which table you put in your visuals. The rule is this: fact tables should always be hidden and slicing should take place only via dimension tables. If you do it in a different way, you'll be sorry one day and your measures will very soon start returning wrong figures. Something you will not even be aware of.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors