Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The question is self explanatory via this picture: https://imgur.com/a/ppUGkJ9
Why is there a discrepancy?
Excel spreadsheet number of starts: 1,757 (correct)
KPI Indicator starts: 1,757 (correct)
Table starts: 1,747 (wrong)
Any ideas? I don't have any filters applied to my Table.
This is happening to all my numbers in the Table.
Here's the measure for Quarter over Quarter calculations in my table. The other measures are just sums of other columns. Eg. Starts, Spec For Sale.
Starts QoQ% =
IF(
ISFILTERED('All Data'[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('All Data'[Starts]),
DATEADD('All Data'[Date].[Date], -1, QUARTER)
)
RETURN
DIVIDE(SUM('All Data'[Starts]) - __PREV_QUARTER, __PREV_QUARTER)
)
And here's for Year over Year:
Starts YoY% =
IF(
ISFILTERED('All Data'[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('All Data'[Starts]),
DATEADD('All Data'[Date].[Date], -1, YEAR)
)
RETURN
DIVIDE(SUM('All Data'[Starts]) - __PREV_YEAR, __PREV_YEAR)
)
Hi. I've figured out the issue.
In my current table I'm using the "Date" field, which I've set to "Date Hierarchy" and thus appears as "Year" and "Quarter" in my table. I used this so that I could calculate the % change YoY and QoQ. This results in my numbers not matching my dataset.
However, when I add "Year" and "Quarter" from my dataset into my table, the numbers in my table match my dataset and are correct. But then I cannot use the formula to calculate the % change YoY and QoQ.
Can I still create a % change formula without having DATEADD? See my above formula.
Hopefully this makes sense.
Hi @Jodes
You may refer to below post.
https://community.powerbi.com/t5/Desktop/Help-with-YOY-DAX-Calculation-with-Filter/td-p/359915
https://community.powerbi.com/t5/Desktop/How-to-show-QoQ-growth/td-p/200813
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |