Using Time Intelligence to calculate Sales in Previous Period

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?

---

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

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.

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

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.

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. 

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

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

Thanks a lot for your support.