Count previous quarter

[JulienH]

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

[JulienH · Accepted solution]

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]

Hi @JulienH ,

I am glad you found a solution. 

Do not hesitate if you need any more help.

Regards,

LC

[lc_finance]

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]

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]

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