Forum Discussion

JulienH's avatar
JulienH
Regular Visitor
6 years ago
Solved

Count previous quarter

Hi all,

 

I am trying to count the numbers of entries in (excel online database) for my previous quarter. If I compare Q1 - 2016 (52 entries) to my Q2 - 2016 previous quarter (30 entries) the amount do not match. I would like to eventually calculate the difference in % from previous quarter.

 

Please help!

 

Articles this quarter = CALCULATE(COUNTX('Article Tracker',DATEADD('Article Tracker'[Publication Date], 0,QUARTER)))
Articles last quarter = CALCULATE(COUNTAX('Article Tracker',DATEADD('Article Tracker'[Publication Date],-1,QUARTER)))

Need Help
  • JulienH's avatar
    JulienH
    6 years ago

    The issue I was having was because i was not using a table calendar which i created using this code.

     

    DimDates = 
VAR BaseCalendar = 
    CALENDARAUTO (3)
RETURN
    GENERATE(
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR(BaseDate)
        VAR MonthNumber = MONTH(BaseDate)
        RETURN ROW (
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT(BaseDate, "mmmm"),
            "Year Month", FORMAT(BaseDate, "mmm yy")
            )
        )

    Once i had that table and linked it to my other tables everything came together.

     

    Article previous quarter = CALCULATE(COUNT('Article Tracker'[Article]),PREVIOUSQUARTER(DimDates[Date]))

     

5 Replies

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    Hi JulienH ,

     

     

    I think the problem is that you are passing the DATEADD as a parameter to the COUNTX function, instead of passing it as a parameter of the CALCULATE function. Moreover, I think that in your case a COUNT formula would be easier, instead of a COUNTX formula.

     

    Here is what I propose as new formulas:

     

    Articles this quarter = CALCULATE(COUNT('Article Tracker'[Article ID]),DATEADD('Article Tracker'[Publication Date], 0,QUARTER))
Articles last quarter = CALCULATE(COUNT('Article Tracker'[Article ID]),DATEADD('Article Tracker'[Publication Date], -1,QUARTER))

    Let me know if those works for you.

     

    Regards,

     

    LC

    Interested in Power BI templates? Check out my blog at www.finance-bi.com

    • JulienH's avatar
      JulienH
      Regular Visitor
      6 years ago

      Thanks for looking into it lc_finance.

       

      The first formula "Article this quarter" seems to work just fine however as i entered the second one i get no results in the table. Also when i use a slicer all charts go in error mode "Can't display the visual. - ... Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion."

       

      Any idea?

       

      • lc_finance's avatar
        lc_finance
        Solution Sage
        6 years ago

        Hi JulienH ,

         

         

        could you share a sample Power BI file?

         

        I'll take a look at it. You can share the file via DropBox, One Drive, Drive or another similar tool.

         

        LC

  • JulienH's avatar
    JulienH
    Regular Visitor
    6 years ago

    The issue I was having was because i was not using a table calendar which i created using this code.

     

    DimDates = 
VAR BaseCalendar = 
    CALENDARAUTO (3)
RETURN
    GENERATE(
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR(BaseDate)
        VAR MonthNumber = MONTH(BaseDate)
        RETURN ROW (
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT(BaseDate, "mmmm"),
            "Year Month", FORMAT(BaseDate, "mmm yy")
            )
        )

    Once i had that table and linked it to my other tables everything came together.

     

    Article previous quarter = CALCULATE(COUNT('Article Tracker'[Article]),PREVIOUSQUARTER(DimDates[Date]))

     

    • lc_finance's avatar
      lc_finance
      Solution Sage
      6 years ago

      Hi JulienH ,

       

      I am glad you found a solution. 

      Do not hesitate if you need any more help.

       

      Regards,

       

      LC