calculate the trend of summited items this quarter with the last quarter

[rssilvaba]

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.

[v-deddai1-msft · Accepted 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

[rssilvaba]

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.

[amitchandak]

@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

[rssilvaba]

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 

[rssilvaba]

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])
)