cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

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
Employee

@Anonymous

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,

11 REPLIES 11
New Member

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
Frequent Visitor

Also yo can use PARALLELPERIOD:

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

Works perfect to me.

Employee

@Anonymous

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,

Frequent Visitor

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
Not applicable

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

Impactful Individual

Hello,

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

Thanks,

Fernando

Helper I

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.

New Member

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.

Skilled Sharer

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

Regular Visitor

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

Anonymous
Not applicable

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.

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors