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.
Hi all,
Everytime I have a visual that involves working with quarters I get lost. trends are fine and easy to calculate. But my issue here is how I can get the data then filter it for the current quarter only and then add another trend line for the quarter before?
Please find a sample file in googledrive.
drive link
Any help is appreciated.
Solved! Go to Solution.
Hi @rssilvaba ,
I'm not very sure with your issue. It works well based on my test for your sample.
For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW6eRZfVX05Dl4rFhL...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @rssilvaba ,
I'm not very sure with your issue. It works well based on my test for your sample.
For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EW6eRZfVX05Dl4rFhL...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
You are right, I had a wrong idea about the trend line and how it should look I realized that if there is not steady increase/decreased the line will always be jagged anyways.
Thanks for sorting out the examples.
@rssilvaba , You have date, You can create a date table and use time intelligence
examples
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Hi @amitchandak, your suggestion did not really work for me. But I managed to create two measures that place the numbers in a stacked chart compared to the last period regardless of the time period.
Now, I tried to use the https://xxlbi.com/blog/simple-linear-regression-in-dax/ which is pretty good but I was not able to plugin the correct values and it looks not correct.
Could anyone take a look in the modified file below and help me with the trend lines for each period?
https://drive.google.com/file/d/1wBrm1e25IVTWSNPjg5YvmvXbbNnpqjOF/view?usp=sharing
Ok I was able to have the trend line pointing to the correct way (I think haha). But if the between the periods we don't have a obvious increasing or declining I dont have a trend line but a line that follows the chart bars. like below:
Were we should still have it as a line in:
You can also notice that the trend line is a bit off or follwing the value at the middle of the chart. I have no idea why. any help is welcome.
Here is the Measure:
# CurrentPeriod Trend =
VAR Known =
FILTER (
SELECTCOLUMNS (
CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), ALLSELECTED ('Calendar') ),
"Known[X]", 'Calendar'[Date],
"Known[Y]", [# CurrentPeriod Escalations]
),
AND ( NOT ( ISBLANK ( Known[X] ) ), NOT ( ISBLANK ( Known[Y] ) ) )
)
/*
SELECTCOLUMNS (
CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), ALLSELECTED ('Calendar') ),
"Known[X]", 'Calendar'[Date],
"Known[Y]", [# CurrentPeriod Escalations]
)
*/
VAR Count_Items =
COUNTROWS ( Known )
VAR Sum_X =
SUMX ( Known, Known[X] )
VAR Sum_X2 =
SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
SUMX ( Known, Known[Y] )
VAR Sum_XY =
SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
AVERAGEX ( Known, Known[X] )
VAR Average_Y =
AVERAGEX ( Known, Known[Y] )
VAR Slope =
DIVIDE (
Count_Items * Sum_XY - Sum_X * Sum_Y,
Count_Items * Sum_X2 - Sum_X ^ 2
)
VAR Intercept = Average_Y
- Slope * Average_X
RETURN
SUMX( DISTINCT( 'Calendar'[Date] ),
(Intercept + Slope * 'Calendar'[Date])
)
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 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |