cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bs1cad Frequent Visitor
Frequent Visitor

Compare data with previous period/last month

Hi There,

 

I currently have a date slicer as quite often we want to be able to look at data for specific set of dates, not just month or year. What I would like to do is create a measure that will look at the previous period to what is selected on the date slicer. So if the range 05/11/16 - 08/11/16 was selected, then the measure would calculate values between the date range 01/11/16 - 04/11/16. Is there also a way to compare with the same period last month, not just last year?

 

Thanks,

Chris

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Compare data with previous period/last month

@bs1cad

 

In DAX, to calculate the same period last month, you can just use DATEADD() to filter your context. Please refer to formula below:

 

same period last month = CALCULATE(SUM('Table'[Amount]),DATEADD('Table'[Date],-1,MONTH))

5.PNG

 

 

Regards,

7 REPLIES 7
Moderator v-sihou-msft
Moderator

Re: Compare data with previous period/last month

@bs1cad

 

In DAX, to calculate the same period last month, you can just use DATEADD() to filter your context. Please refer to formula below:

 

same period last month = CALCULATE(SUM('Table'[Amount]),DATEADD('Table'[Date],-1,MONTH))

5.PNG

 

 

Regards,

Ustinovdl Regular Visitor
Regular Visitor

Re: Compare data with previous period/last month

What do I do wrong?

Month field is like = Table[date_field].month

It doesn't work.

When I change -1 period for DATEADD to 1 I receive a correct picture with the incorrect numbers.

I tried to make the same as you but it didn't work.

1.jpg

ReenBB Visitor
Visitor

Re: Compare data with previous period/last month

I'm having the same problem. Anyone else come up with a solution/fix for this?

gswilliams1906 Occasional Visitor
Occasional Visitor

Re: Compare data with previous period/last month

Not sure if this was still needed, but I needed and wished this answer was available.  You need to have (or what worked for me) a  continuous date table and create a relationship with your data's date to the continuous date.  Of course, if your data's date include time.  You will need to add a date only column to your data.  Once I did the above, the date formulas (built-in and mine) started to work correctly.

AshleyMartinez Frequent Visitor
Frequent Visitor

Re: Compare data with previous period/last month

Also yo can use PARALLELPERIOD:

 

same period last month = CALCULATE(SUM('Table'[Amount]),PARALLELPERIOD('Table'[Date],-1,MONTH))

Works perfect to me. 

nickchobotar Established Member
Established Member

Re: Compare data with previous period/last month

@Ustinovdl

 

DATEADD will work only with consecutive days. So, if that's not the case in your Zapros1[pay_day]  you will get wrong results back.

 

N -

calerof Member
Member

Re: Compare data with previous period/last month

Hello,

 

Do you know if it would be possible to add the week interval to the DATEADD function?

 

Thanks,

 

Fernando