Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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_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_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
Solved! Go to Solution.
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.
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.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
21 | |
17 | |
16 | |
11 | |
7 |
User | Count |
---|---|
26 | |
25 | |
12 | |
12 | |
12 |