I am calculating QoQ variances and one challenge I am having is when data is not present for a given quarter. As a result, my measure only works when I have data in sequential order quarter over quarter. See below screenshot as example. I have 'cycle time [days] for 2022Q1, 2021Q3 and 2021Q2 but since I have a gap in Q4, the measure is only calculating variance between Q3 and Q2 in 2021.
Any tips or advice is greatly appreciated. I have attached the query below along with a screenshot of the table visualization. I have also attached a dummy pbix file
DummyFile.pbix (70.0 KB)
@JoeRobert Hello, Joe!
you could add If or Switch construction in your current table measures
“well count (closed)”, “well count (open)” and “cycle time [days]”
so they report 0 value in case Asset Class / Year / Business Unit / Well name column rows do not have values in Q row (like 2021 Q4).
It would give you opportunity to manage result measure
like ex:
(new) Cycle Time (days) =
IF (HASONEVALUE(‘Table’[Asset Class]),
SUM (‘Table’[Cycle Time [Days]]]) , 0)
this measure will return 0 values for all quarters
Hope, it helps you!
BR,
Oleg
It’s great to know that you are making progress with your query @JoeRobert.
Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.
We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hello @JoeRobert, it’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @JoeRobert, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.
Thanks Oleg for the quick response and apologies for the late reply; i got caught up with the work week.
I am not trying to return a 0 value for the blank quarter but instead I want the calculation to skip the blank quarters.
Here is the query for one of the measures called [QoQ (%)] for Cycle Time (days):
[PQ Selected Cycle Time [Days]]] =
VAR PQ = SELECTEDVALUE('Date (Metric Tables)'[QUARTER]) -1
VAR CY = SELECTEDVALUE('Date (Metric Tables)'[YEAR])
VAR CQ = SELECTEDVALUE('Date (Metric Tables)'[YEAR QUARTER])
VAR PQMEASURE =
CALCULATE(
'Measure Table'[Selected Cycle Time [Days]]],
PREVIOUSQUARTER('Date (Metric Tables)'[Date]))
VAR CQMEASURE =
CALCULATE(
'Measure Table'[Selected Cycle Time [Days]]],
FILTER(ALL('DATE (Metric Tables)'), 'Date (Metric Tables)'[Year Quarter] = CQ))
RETURN
SWITCH(TRUE(),
CQMEASURE = BLANK(), BLANK(),
DIVIDE(CQMEASURE-PQMEASURE, PQMEASURE))
Here is a screenshot of the table visual where I have put a box around the measure above:
Now when you do the manual calcs for the quarter over quarter variance with cycle time (days), this is what you get:
2022Q1 = ((3.1 - 4.1)/4.1) = -24%
2021Q4 = ((4.1 - 3.9)/3.9) = +5%
2021Q3 = ((3.9 - 2.4)/2.4) = +63%
Highlighted above in bold and referencing the screenshot, you can see that the measure is not returning the correct variance for 2021Q3 because Q2 is blank (no operations during this time). Instead of returning a blank, i want to skip that quarter and calculate with the 2020Q1 value which will return +63%.
After thinking harder, i was able to come up with a solution on the dummy file; see below ‘test’ query and screenshot. I ended up removing the PREVIOUSQUARTER() function and replaced with a filter argument that went back two quarters. And in order to make this happen, i had to create a IndexQuarter column that properly aggregated across the right quarters. I don’t think it is the most efficient method and not to mention, i will need to create a new custom column in my Date table just to accommodate this one measure. Any other suggestions is greatly appreciated
[test PQ Cycle Time [Days]]] =
VAR PQ = SELECTEDVALUE('Date Table'[Quarter]) -1
VAR CY = SELECTEDVALUE('Date Table'[Year])
VAR CQ = SELECTEDVALUE('Date Table'[Year Quarter])
VAR PQ1MEASURE =
CALCULATE(
MEDIAN('Table'[Cycle Time [Days]]]),
FILTER(ALLSELECTED('Date Table'), 'Date Table'[IndexQuarter] = MAX('Date Table'[IndexQuarter])-1))
--PREVIOUSQUARTER('Date Table'[Date]))
VAR PQ2MEASURE =
CALCULATE(
MEDIAN('Table'[Cycle Time [Days]]]),
FILTER(ALLSELECTED('Date Table'), 'Date Table'[IndexQuarter] = MAX('Date Table'[IndexQuarter])-2))
--PREVIOUSQUARTER('Date Table'[Date]))
VAR PQMEASURE =
SWITCH(TRUE(),
PQ1MEASURE = BLANK(), PQ2MEASURE,
PQ1MEASURE)
VAR CQMEASURE =
CALCULATE(
MEDIAN('Table'[Cycle Time [Days]]]),
FILTER(ALL('Date Table'), 'Date Table'[Year Quarter] = CQ))
RETURN
SWITCH(TRUE(),
CQMEASURE = BLANK(), BLANK(),
DIVIDE(CQMEASURE-PQMEASURE, PQMEASURE))
DummyFile.pbix (78.4 KB)
1 Like
@JoeRobert See if this helps:
1 Like
Hello @JoeRobert, just following up if the response above help you solve your inquiry?
If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
Thanks for sharing! I’ll check this out in the morning
Great video! This methodology you shared in the video, filtering down the date table based off sales, is exactly what I need in my situation. I will lift/shift when I get back to my computer and post the results
2 Likes
I tried this method on the dummy file and it worked. This solution is more efficient because I do not have to write how many quarters to go back in the calculation, which is already captured in the filter argument. I still have to include the new custom column in my date table because my original [Year Quarter] column is text string but this should be a easy fix since my date table is in the dataflow. Below is the ‘test 2’ measure using the new DAX query. The only adjustment I needed to make was to include an FILTER/ALLSELECTED() function in the [PQMEASURE] variable. Thanks again for the help
DummyFile.pbix (80.3 KB)