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,

View solution in original post

8 REPLIES 8
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,

View solution in original post

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 -

Highlighted
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

 

NochWyn Frequent Visitor
Frequent Visitor

Re: Compare data with previous period/last month

This works for me only if the dates are the dimensional row. If I apply a filter to show one month at a time with a different dimension as the row, none of these methods (DATEADD, PREVIOUSMONTH, PARALLELPERIOD) work. Any ideas?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 118 members 1,480 guests
Please welcome our newest community members: