comparing data same period prior quarter (ex. July v April or Aug v May)?

[Olia]

Hello,

I was asked to create a report that can show a comparison in costs to the same period last quarter, but I have no clue how.

So Month 1 of Q2 (April) needs to be compared to Month 1 of Q3 (July). I need to be able to show the difference between the two (both in real terms and in %).

Is there any way to do this?

[v-frfei-msft · Accepted solution]

Hi @Olia,

Based on my test, we can take the following steps to meet your requirement.

1.Enter the data and create a date table, and get the year and month column.

dimtime = CALENDARAUTO()

yymm = YEAR(dimtime[Date])*100+MONTH(dimtime[Date])

2.Create the measures as below, then we can get the result as we need.

previous = CALCULATE(SUM(Table1[sale]),DATEADD(dimtime[Date],-3,MONTH))

perc = IF(ISBLANK(SUM(Table1[sale])) || ISBLANK( [previous]),BLANK(),SUM(Table1[sale])-[previous])/SUM(Table1[sale])

For more details, please check the pbix as attached.

https://www.dropbox.com/s/i1v23ak9nbtw51f/comparing%20data%20same%20period%20prior%20quarter%20%28ex...

Regards,

Frank

[v-frfei-msft]

Hi @Olia,

Does that make sense? If so, kindly mark my answer as a solution to close the case,

Best Regards,
Frank

[Olia]

Hi @v-frfei-msft Frank,

Thank you for bumping the post, I must have overlooked it.

I have tried your solution, but by applying it I get ino trouble on the main sheet. So I must be overlooking something. Can you please help?

I have created a Dimtime table like you suggested, and linked the Date part of it to the Date of the main table.

suddenly my Change GC measure, which was supposed to show the Change compared to Previous Month stopped working. It was using the Date from the main sheet (let's call it Sheet1)

do you have any clue as to what might have happened?

edit: if I replace 'Sheet1'[date].[date] by 'Dimtime'[date].[date] the measures give wrong or no results