Forum Discussion

globalwarme's avatar
globalwarme
Frequent Visitor
6 years ago
Solved

Trend analysis

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?
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    6 years ago

    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/

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      globalwarme
      Frequent Visitor
      6 years ago

      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's avatar
        Icey
        Community Support
        6 years ago

        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.