Compare data with previous period/last month

[bs1cad]

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

[srinivg]

I'm not getting the same period total sales of previous month value in respective month row, instead im getting total amount. 

same period last month = CALCULATE(SUM(financials[ Sales]),DATEADD(financials[Date],-1,MONTH)) im using this DAX

[AshleyMartinez]

Also yo can use PARALLELPERIOD:

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

Works perfect to me. 

[v-sihou-msft · Accepted solution]

@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))

Regards,

[NochWyn]

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?

[Anonymous]

I am facing similar issue where without date dimension calculated previous period is not working properly. Any help and lead would be appreciated.

[calerof]

Hello,

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

Thanks,

Fernando

[Ustinovdl]

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.

[bashhwu]

What you've got is right because using 1 means you are calculating the meseaure for the next month not the previous month which requires (-1) to be set. The table clearly shows that December has no next month.

[nickchobotar]

@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 -

[ReenBB]

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

[Anonymous]

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.