Time-intelligence functions : dateadd vs previousquarter

[amaugerb]

Hi all,

I'm a new Power BI user and I try to understand how it works.

I have a simple report with 2 tables : one calendar (with every days between 2000 and 2025) and one with data.

Here is the very small dataset I used for my tests :

I then made two new mesures :

value_q1 = CALCULATE(SUM(Data[value]), DATEADD('Calendar'[Date],-1,QUARTER))
value_q2 = CALCULATE(SUM(Data[value]), PREVIOUSQUARTER('Calendar'[Date]))

And finally, a small report :

I now have 2 questions :

1. why is the Total empty for value_q2?
2. why is the value_q1 correct for Q2? There is no date D where DATEADD(D, -1, QUARTER) would give january 31st.

DATEADD(2020-04-30, -1, QUARTER) = 2020-01-30
DATEADD(2020-05-01, -1, QUARTER) = 2020-02-01

Therefore, I see no reason why the value_q1 would include the value for january 31st from a pure technical point of vue.

The definition of PREVIOUSQUARTER states clearly that the function will build "all dates from the previous quarter, based on the first date" in the current context.

Returns a table that contains a column of all dates from the previous quarter, based on the first date in the dates column, in the current context.

With that in mind, I understand why my result should be correct with that function.

However I see DATEADD as a simple shift of dates from current context:

Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

And I don't see why the SUM is correct in that case.

If I build a report with the calendar table and a new column :

PreviousQuarter = DATEADD('Calendar'[Date], -1, QUARTER)

I won't have the date 2020-01-31 in that PreviousQuarter column and therefore, I shouldn't have the correct sum.

Thank for your help 🙂

[v-lili6-msft]

HI  @amaugerb 

You could refer to this post:

https://community.powerbi.com/t5/Desktop/PREVIOUSMONTH/td-p/13307

Regards,

Lin