Forum Discussion
Quick Measure QoQ % Change Showing Incorrect Data
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
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
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,
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!
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.