Trend analysis

[globalwarme]

I am trying to create a chart that identifies categories where sales in a quarter are greater than in the previous quarter.  I created two measures as follows: 

Q interaction $ = TOTALQTD(SUM(clients[Interaction $]),(interactions[startDate]))

LQ interaction $ = CALCULATE(sum(clients[Interaction $]),PREVIOUSQUARTER(interactions[startDate]))

I then created a colour coding measure as follows:

Colour = SWITCH(true(),clients[Q interaction $] > clients[LQ interaction $], "#OOAEEE", clients[Q interaction $] < clients[LQ interaction $], "#FF0318", "#FFF000")

I then add some conditional formatting to my report.  This actually works when I use the date slicer "between" function (between two dates). 

However when I use the date hierarchy and display quarters, the conditional formatting does not work.  Any ideas how to make this work with the date slicer so I can just choose quarter rather than settng between dates?

[amitchandak · Accepted solution]

It should work. I prefer to use a date table for all this. Formula with Datesqtd or totalqtd

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))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(ENDOFQUARTER(dateadd('Date'[Date],-1,Year))))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[globalwarme]

Yes, creating a date table solved my problem.  Many thanks for this.  One follow up question here: I created a date table with 20 years of dates just to be on the safe side, but when I add one of the new date fields to a slicer, I am getting a list of 20 years of dates.  Is there any way to limit this within the slicer, or should I simply delete a few years from my date table.

thanks again

P

[Icey]

Hi @globalwarme ,

You can create a Measure like so:

Date Filter =
IF (
    MAX ( Calendar[Date] ) <= DATE ( 2020, 4, 8 )
        && MAX ( Calendar[Date] ) >= DATE ( 2019, 1, 1 ),
    1
)

Then, put the measure on "Filters on this visual" of the slicer, and set [Date Filter] is 1.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490