Quick Measure QoQ % Change Showing Incorrect Data

[Anonymous]

Hi All,

I am trying to create a quick measure (QoQ % change) with a base value derived from another measure ("Time to First Attempt New") which calculates the average time for someone in the dataset to attempt a first test. Please see pictures below (due to data sharing constraints) but I am hoping that is enough...can someone explain why the percent changes quarter to quarter are off? They start out in the right ball park but then change wildly....the DAX calculation looks right to me, but disclaimer I am very new to DAX/Quick Measures in PBI....so I could be missing something very basic. My date column is of type date from my original table, using the same date hierarchy for all visuals and there is data back to 2016 but I have cut it off starting in 2018....any help is much appreciated!

DAX formula plus quick measure chosenGeneral visual I'm attempting to createQuick measure perameters

[amitchandak · Accepted solution]

Try Using these

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date Filer])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],-1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],-1,Year)))

and calculate change % on that. Not if you need complete qtr

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi @amitchandak  when I attempt your measures (substituting your sales for my "avg time to attempt first test"), and then do % change = (Current QTD - Last QTD / Last QTD)*100, it shows data in a line chart but only year by year. When I filtered down the date hierarchy to look at both year and quarter, I get an error saying "infinite" values, and no data shows up. Do I need to create a reference date table for this to work?

Any help much appreciated!

[Anonymous]

@amitchandak  apologies - my percentages were initially off due to multiple people in the dashboard changing filters on my continuos variables I was measuring.

Will now mark as completed! One note if it helps anyone - using Power BI's built in quick measure for QoQ% change automatically seems to handle the "infinity" data issue mentioned above, the only thing I had to do in order to filter out the years of data not needed (2016-2018)  was create two copies of my original "date" (MM-DD-YYYY) column from my fact table and transform one into Quarter #'s from "Transform" tab of Query Editor,  then extract the year from the other "date" column copy (YYYY), and merge year and quarter together (i.e. 2016/Q1, 2016/Q2 etc.) so the year/quarter merged column could then be used to filter out the years I didn't want on the line graph (i.e. anything before 2018 so I could focus the graph on 2018-19) When I try to use my original "date" column to filter those years out I received an error I've seen elsewhere about the primary date column being the only value capable of filtering or grouping the data.

[Anonymous]

@amitchandak thank you! I will try this solution tomorrow - one question,  based on your blog do I need to create a date reference table and link that "date filer" field that to my "date" field (above) from my main data table? Currently the date I am pulling from is from my main data table ("PFT w/ Proximity") with all my other fields.

I have seen other people set up a reference date table with CALENDARAUTO() functions or others in the past for similar problems and connect the two tables. If so, why?

Respectfully,