Rolling growth percentage last 30 days or 12 months

[superroy]

Hi,

I want to build a graphic with Power BI displaying the order growth percentage in last 30 days or 12 months.

I have a table 'orders':

id	date
10001	2016-12-12 11:16:00
10002	2016-12-12 11:17:00
10003	2016-12-12 11:18:00

I would like to display the rolling growth percentage of the number of orders of the last 30 days in comparison to the same period (30 days) last year.

I also would like this for last 12 months.

How to accomplish this?

[superroy]

I have a problem with getting the last 30 days.

Linked to my orders table I now have a table 'Date'

With one column Date = CALENDAR("2015-Jan-01";TODAY())

Then I have table linked to that 'DatePeriod'

DatePeriod =
UNION (
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' ; DATESBETWEEN('Date'[Date];today()-07;today()-1) ); 'Date'[Date]);"Period";"Last 07 Days") ;
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' ; DATESBETWEEN('Date'[Date];today()-14;today()-1) ); 'Date'[Date]);"Period";"Last 14 Days") ; ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' ; DATESBETWEEN('Date'[Date];today()-30;today()-1) ); 'Date'[Date]);"Period";"Last 30 Days") ;
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' ; DATESBETWEEN('Date'[Date];today()-90;today()-1) ); 'Date'[Date]);"Period";"Last 90 Days") ; ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' ; DATESBETWEEN('Date'[Date];today()-365;today()-1) ); 'Date'[Date]);"Period";"Last 365 Days") ;
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'); 'Date'[Date]);"Period";"All")
)

On my 'orders' table I now have the following measures:

NumOrders = DISTINCTCOUNT(Orders[id])

PreviousYear = CALCULATE([NumOrders];SAMEPERIODLASTYEAR(Orders[date]))

But every time I want the last 30 days viewed there's no data in PreviousYear

[jblackshear]

I don't know everything. I'm still new, so I don't even know much.

But this problem is one I encountered, and was a huge learning experience for me. And since then, I have seen others encounter the problem.

Your calculation:

PreviousYear = CALCULATE([NumOrders];SAMEPERIODLASTYEAR(Orders[date]))

If you have a filter that is showing you only the current period, that filter is restricting the data available to your calculation. Your CALCULATE function can't see the previous year's data, even though it is available in the data set. To fix this, you need to open up the filter within your calculation so that it can see the previous year's data.

PreviousYear = CALCULATE([NumOrders], FILTER(ALL(Date)), SAMPERIODLASTYEAR(Date[Date]))

One other note: you may want to expand your Date table to include more standard Calendar table columns in addition to the Date column you have. I'm not sure if it's strictly necessary for the one calculation we are discussing here - but it might be. There is a way to have PowerBI create a full-fledged table for you, but in my case I simply imported my Calendar table from the data warehouse, so I haven't used the internally-built feature yet.

[superroy]

When I build PreviousYear like:

PreviousYear = CALCULATE([NumOrders]; FILTER(ALL('Date'); 'Date'[Date].[Year] = MAX('Date'[Date].[Year]) -1); SAMEPERIODLASTYEAR('DatePeriod'[Date]))

I have all the correct PreviousYear data for 2016.

But as soon as I select Last 30 days as a page level filter, than my PreviousYear data is blank.

[MattAllington · Accepted solution]

---

@superroy wrote:

When I build PreviousYear like:

PreviousYear = CALCULATE([NumOrders]; FILTER(ALL('Date'); 'Date'[Date].[Year] = MAX('Date'[Date].[Year]) -1); SAMEPERIODLASTYEAR('DatePeriod'[Date]))

I have all the correct PreviousYear data for 2016.

But as soon as I select Last 30 days as a page level filter, than my PreviousYear data is blank.

---

SAMEPERIODLASTYEAR is an inbuilt time intelligence function.  These only work if you are using a calendar table with daily granularity.  Assuming you are doing that, then you don't need the filter portion

PreviousYear = CALCULATE([NumOrders]; SAMEPERIODLASTYEAR('Date'[Date]))

Note the flip to use the calendar table. What is DatePeriod?  This may or many not work depending what this table is 

[jeffmjohnson330]

I have searched everywhere and finally this solution actually worked, thanks! I am trying to do the same thing, but use the previous quarter. I thought it would work with DATEADD like this:

PreviousQTR = CALCULATE([NumOrders]; FILTER(ALL('Date'); 'Date'[Date].[Quarter] = MAX('Date'[Date].[Quarter]) -1); DATEADD('DatePeriod'[Date],-1,QUARTER))

Do you know why this wouldn't work or know how to fix it?

[jblackshear]

I think your calculation can be simplified. You are simply comparing the number of orders from the current (or selected) quarter against the number of orders from the previous quarter? Try PARALLELPERIOD. (Also, I use "," between arguments, rather than ";". I honestly don't know what the significance is, if any - is that an option? or a localization setting?)

From my own code:

Purchases Previous Year = CALCULATE('Sales'[Purchases], PARALLELPERIOD('Calendar'[Date], -12, MONTH))

(I used -12 months rather than -1 year because it's a rolling-12 month rather than a calendar year)

[jeffmjohnson330]

That formula doesn't work. That is what I had been seeing all over until I came across the previously mentioned formula. When I put it on a line graph with dates it is completely blank. Also I think this new formula adds up the numbers over a period rather than just comparing the numbers from the same date a year ago.

I also just use the "," and it is the only way it works on my computer. I was just copying the formula the previous person had put down.

[jblackshear]

I'm curious. Can you show a measure for current period and your PreviousYear measure side-by-side?

I'm wondering if you're just showing PreviousYear alone, and if it has no context?

So, for example, build a matrix visual, include dates, a measure calculating the total for the current period (I'm assuming month?) and your PreviousYear measure. Try it unfiltered and then filtered.

Also, be sure your Dates table has every date filled in - no skips. You would probably have gotten an error message if that's what happened, but just want to point that out.

It also may be a good time to try the PowerBI generated full calendar table with all the fields (year, month, etc.) At one point in my development, I had a bug (I guess?) in my calendar table - it looked fine but the calculations just wouldn't work quite right and I frequently got no data. I rebuilt the calendar table to convert an integer data type to date datatype in the SQL rather than in the query editor - and it worked perfectly after that. I offer that only to suggest that the date intelligence functions seem to be very particular - so give them what they want.

[MattAllington]

You will need a calendar table if you want to use inbuilt time Intelligence as suggested above. . 

http://exceleratorbi.com.au/power-pivot-calendar-tables/

You should also also either remove the time stamps from your dates or split them I to a separate column befor joining to your new calendar table. 

[Thiyags]

You can use the SAMEPERIODLASTYEAR function

CALCULATE(SUM(Sheet1[Sales]),SAMEPERIODLASTYEAR(Sheet1[Date ].[Date]))