Using Time Intelligence to calculate Sales in Previous Period

[Andwu]

Hi everyone,

I'd like to use the Time Intelligence functionalities in PowerBI Desktop, however they do not work as expected.

The data model is quite simple. I have a table named Sales from excel containing the sales (column Actuals) per product line, country, and month. The month is encoded as first day of the month, e.g. the sales for January 2016 are stored with the date 01.01.2016. The column has data type date.

Further I have a second table named date with columns date (dd.mm.yyyy), year (derived from date with YEAR()). This table has been created in PowerBI directly with the calendar() Function. Both tables are linked via the Date Column

My formula to calculate the sales of the previous month is:

Actuals Prev = CALCULATE(SUM('Sales'[Actuals]); PARALLELPERIOD('Sales'[Date];-1;MONTH))

This works fine so far. However, I'd like to have slighly different behaviour.

In my dashboard I have a slicer on Year, a bar chart showing the actual sales for the selected year per month. In addition to that; i have a matrix, containing the actual sales and the sales of the previous period per product line. In the example below, the slicer is always set to year 2015.

The matrix is changing based on the month that I select in my bar chart, e.g. if I select April 2015, the column actual prev shows the sales for March 2015. However if I select January 2015, I still like to see as actual prev the values for December 2014. This is the first issue that doesn't work.

The second issue is, that I'd like to use the calculation as well in case I select several month, e.g. if I select June & July, my Actual prev should show me the sum of April and May. Further in case I do not select anythink, the Actual prev should show me the sum of the values of 2014.

Any help is appreciated.

Thanks

[MattAllington]

You are using he wrong function. swap PARALLELPERIOD with either SAMEPERIODLASTYEAR or DATEADD.  

[Andwu]

Hi matt,

thanks for your reply. I tried that, but that doesn't solve my issues...

Any other ideas?

Thanks

[MattAllington]

well it should work, but clearly something is wrong.  Any chance you can post a sample (or actual) workbook?

[Andwu]

Sure,

here is the example. It is based on the Superstore example.

Power BI Sales Example

Hope you can access it.

[MattAllington]

Yes I can access it, and it looks to be working perfectly to me.  IN Feb, the sales prior month equal Jan.  What am I missing?

[Andwu]

In 1/01/2012 I'd like to see the sales for 1/12/2011 as well as sales for the previous month, as the base data is available. Further I'd like to see the cumulated previous sales in case I select several periods, e.g. when I remove the drill down by month from the matrix and select november and december, i'd like to see the sales of september and october as previous sales.

[MattAllington]

---

@Andwu wrote:

In 1/01/2012 I'd like to see the sales for 1/12/2011 as well as sales for the previous month, as the base data is available. Further I'd like to see the cumulated previous sales in case I select several periods, e.g. when I remove the drill down by month from the matrix and select november and december, i'd like to see the sales of september and october as previous sales.

---

Sorry, it was late!  OK, 2 issues.  Firstly your visulalisations have the date column from the Sales table.  This is incorrect - you should always use the columns from your date table in the visualisations.  You can even hide the date column from the sales table to avoid confusion.  Secondly the date column in your formula is also from the wrong table.  Switch to the Date[Date] column

[Andwu]

Hi,

great, that solves one of my issues. Thank you very much.

To fix the second one, I must somehow adjust the offset / number of interval and the interval automatically based on the selection.

DATEADD('Date'[Date];-1;MONTH))

Regarding the interval I can work with a slicer in Excel, however I'm not sure if this works in PowerBI Desktop as well.

Regarding the number of interval: Is there any possibility to fetch the number of months that have been selected by the user?

[MattAllington]

---

@Andwu wrote:

Is there any possibility to fetch the number of months that have been selected by the user?

---

Yes. 

1.  Create a simple table with a list of the possible values

2. add this table to the data model

3. Add a slicer to the pivot table so the user can select the value

4 write a measure that "harvests" the selected value.  somthing like Selected Value=max(Table[Value]) will do

5.  Add the value into your formula.

I have never tested this inside a DATEADD formula but it is worth a try

[Andwu]

I tried to capture the difference in month from the selection with

Div = MONTH(LASTDATE('Date'[Date])) - MONTH(Firstdate('Date'[Date]))

however, the result is always 0, as the filter is not considered appropriatly.

I want to avoid to add a new slicer. Instead I'd like to filter the table based on the bars that have been selected by the user, e.g. the user selects november and december, I would expect a difference of 1 between the month.

[MattAllington]

Can you post an image of you visualisation. The formula depends on what the visual looks like - specifically what filtering is occurring. 

[MattAllington]

In the top right matrix, which table does the "month" come from. It should be from the date table. 

I would think to check the number of months selected, you would use =distinctcount(date[month]). This assumes you are not using any columns from the sales table on your visualisations. 

[Andwu]

The month comes from the Date table.

With

=distinctcount(date[month])

I always get 12, even if I select one or two bars in my chart.

I do not get your comment "This assumes you are not using any columns from the sales table on your visualization".

Of course I have use some columns from this table, as in this table there are my facts, so I don't get it.

Thanks

[MattAllington · Accepted solution]

---

@Andwu wrote:
 

I do not get your comment "This assumes you are not using any columns from the sales table on your visualization".

Of course I have use some columns from this table, as in this table there are my facts, so I don't get it.

---

I was refering to the columns slicing your data, not the data itself.  If you use a column from your sales table in your slicers, then this will not work.

I just tested this and it works for me.  

[Andwu]

That's it. I created the count as column, not as measure.

Thanks a lot for your support.

[Andwu]

Yes, sure. as you can see on the screenshot, I have marked three columns, thus in the end I'd like to have six rows in the right table. Month 7 - 9 with sales previous period, Month 10 - 12 with sales current period.

[kcantor]

If, in 1/1/12 you want to see 1/1/11 you need to do dateadd, -1, year to go back and look at year over year.You are using -1 month which is only taking it back 1 month.