Compare two periods using different slicers (Just like its done in Google Analytics)

[Anonymous]

Hello Experts,

I need to implement two slicers one for current period and one for the Comparison period.

There would be a line chart which will show yearly/quarterly/monthly/daily values for the number of newly registered users based on current period date range slicer (slicer 1).

You could select a different period in the comparison slicer and the same has to be plotted as a new line in the existing line chart. So it could be a daily data for a week compared to daily data last week or some week of last year (for that instance any random period).

Below is such an implementation in the google analytics dashboard. Ignore the comment "compare june of last year to june of this year". Basically it can be any period.

Assume that the model is very straight forward. It has a fact table FactCustomers with fields Date and NoOfCustomers.

There is a Calendar table which joins to this fact based on date.

Hopefully someone has done this before.

Thanks in advance!

Kind regards,

Chetan 

[v-jayw-msft]

Hi @Anonymous ,

If you are using two slicers with two different periods (not just previous period) then how you deal with the x-axis?

Best Regards,

Jay

[Anonymous]

Hi Jay,

The x-axis will only be based on the current slicer selection and not the previous one.

Although it would be great to have a dynamic one where the x-axis is determined by the Min period between current and previous slicer and the maximum of the same.

[amitchandak]

@Anonymous , if this need to be just a year  behind, you can do with one slicer and a year behind the measure

You can display the range of last year

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd("Date"[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR("Date"[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi Amit,

 unfortunately it is not that simple requirement.

the slicer does not show only previous year but can also be selected to show other date ranges.

Appreciate your effort but this is not what i am looking for.

[Anonymous]

@Anonymous please, how you sorted this out?

[Anonymous · Accepted solution]

https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/

this is the exact approach that I have used. The trick is to create a calendar table for previous period and have an inactive relationship between the original Calendar table and the newly created one. The calculations can be handled in DAX using the USERELATIONSHIP function.

[Anonymous]

something better than this? https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/