# Previous quarter not working



## grapevine (May 21, 2017)

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 (May 21, 2017)

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


----------



## grapevine (May 21, 2017)

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 (May 21, 2017)

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 (May 21, 2017)

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 (May 22, 2017)

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 (May 22, 2017)

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


----------



## grapevine (May 22, 2017)

gazpage said:


> 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


----------

