Trying to create a rolling period delta, but am conflicting with my date filters.

[warrencowan]

I am trying to create a custom measure, to reflect the change in sum of visits, from the previous reporting period and the current one. But I am being scuppered by my date filters as I'm filtering the data out.

Current setup

I have a website traffic table, with visits and dates.

I also have a separate dates table, and a period table that contains a handful of rollup summary periods, such as 'last 30 days', 'month to date' etc. This was based on an excellent post by chris webb on https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi...

So I have a measure for the visits in the selected period from my traffic table, and I am now trying to create a delta measure for visits, that will allow me to see the sum of visits in the previous range. So if I select last 30 days form my period filter, I want my delta visits measure to give me the sum of visits in the prior 30 days, to the current 30 days selected in the summary period filter.

However, I get a blank return in my attempts, as I suspect I am filtering out the prior dates, and hence have no data available to the measure.

Can anyone help?

Please forgive the very sloppy way I have performed this, but it was more for my primitive sanity.

I have been trying to achieve this with a calculate measure,

Prior period Visits = CALCULATE(SUM(‘website traffic'[visits])   ,  DATESBETWEEN(Period[Date]  ,  [Previous period start]  ,  [Previous period end]))

The 2 period start and end measures are other custom measures, that use the start and end dates in the range, and count backwards by the number of dates in the range, to work out the preceding start and end points of the prior range.

Previous period end = (LASTDATE(Period[Date])-[Days in range])

Previous period start = (FIRSTDATE(Period[Date])-[Days in range])

Days in range = DISTINCTCOUNT(‘website traffic'[Date])

Like I said…a bit sloppy, but was just trying to break it up into chunks so I could work out where it was going wrong.

Anyway…I can’t get it to work.

All pointers greatly appreciated.

Many thnx in advance

Warren

[v-micsh-msft]

Hi Warren,

Could you please explain a bit regarding the following two measures, why they should minus the Days in range?

Previous period end = (LASTDATE(Period[Date])-[Days in range])

Previous period start = (FIRSTDATE(Period[Date])-[Days in range])

LASTDATE() and FIRSTDATE() function would automatically generate the last and first date from the current date context, I suggest that we can remove the [Days in range] here and see what the result is.

If this still not goes to work, please share some of your data model, then we could offer some more suitable expressions.

Regards