Previous quarter not working

[grapevine]

I am relatively new to DAX and want to write a formula to compare this quarter with last quarter. I did not have a calendar table to I created one using the 2016 New Date Table command and then linked the Date fields together. Hopefully this is correct.

The formulas I have written are as follows:
This quarter

=CALCULATE(sum([Profit]),dateadd('Calendar'[Date],0,QUARTER))

Previous quarter

=CALCULATE(sum([Profit]),DATEADD('Calendar'[Date],-1,QUARTER))

When I display the results on the screen it just gives me the total alongside the current year so something is not calculating correctly
eg
2016 PQ = 30
2016 CQ = 30
2017 PQ = 7.5
2017 CQ = 7.5
Total PQ = 37.50
Total CQ = 37.50

which means if I subtract current quarter from previous quarter I always end up with 0
Can someone please point me in the right direction
Many thanks

 

[gazpage]

What is the context that you are evaluating these in? What does your Date column look like and which quarter are you using?

 

[grapevine]

My date column in the original excel spreadsheet looks like this 31/03/2017, in the PowerPivot table it looks like 31/03/2017 00:00:00
In the calendar table I created from the Date Table Icon on the Design Tab in the PowerPivot window looks like 31/03/2017 00:00:00

The data sheet has a list of dates against which the cost of expenditure is recorded. I want to see whether the total spent of a client has gone up or down over the last quarter compared to the previous quarter and if possible I would like to be on a rolling three month so I don't have to do any intervention.

I hope this helps; if you want any more information please do let me know
Many thanks

 

[gazpage]

Sorry, I didn't really ask the right question.

Is the Calendar [Date] column a daily list of dates with no gaps?
do I understand correctly that you want to do this calculation for every single day, i.e. On 7 July 2016 the current quarter is 8 April 2016 to 7 July 2016 and the previous quarter the three months before that? If so Dateadd is not the right function as it shifts a block of dates back or forward.

 

[gazpage]

If that is what you want to do, take a look at the DATESINPERIOD function.

Something like.

Current Quarter:=
CALCULATE(
    SUM([Profit]),
    DATESINPERIOD(
        Calendar[Date],
        LASTDATE(Calendar[Date]),
        -1,
        QUARTER
    )
)

 

[grapevine]

Sorry, only just seen your previous post. The Calendar Table was automatically created by clicking on the icon in the Design Tab and using Date Table.
I have just checked and the list of dates runs from 01/01//2013 to the 31/12/2017 without any gaps in the dates. The following columns were created and I have given an example of the data created

Date = 03/01/2013 00:00:00
Year = 2013
Month Number = 1
Month = January
MMM-YYYY = Jan-2013
Day of Week Number = 3
Day of Week = Tuesday

In my spreadsheet the dates run from 31/07/2013 to the 31/03/2017

The data is added on a monthly basis so all the dates in the spreadsheet are formatted as the end of the month 30/04/2017, 31/05/2017 etc. There are dates in everyrow and a profit figure in every row. I don't need a daily rolling figure as there are no individual days listed within the spreadsheet. I hope this helps

 

[gazpage]

Yeah, try DATESINPERIOD then and for the previous, use DATESADD to shift back a quarter.

 

[grapevine]

Yeah, try DATESINPERIOD then and for the previous, use DATESADD to shift back a quarter.

I could not work out why I was not getting any values and in despair, I started another PivotTable, pulled in the Measure and hey presto it is working. No idea why this should be by thankfully all is now good. I have used the above to get the differences and it is correctly showing.
Thank you, I have learnt a lot