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

     

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