Group-Comparisons of different time periods in one data source

[SimonGrams]

Hello,

my data base contains different weeks, which are split into different countries and some other segments.

I try to build a view where I can compare different week-periods (which I can choose by filter) by the same segment-filters.

What have I done so far?

To compare different week-periods I duplicated my data base. Now I have 2 independent week-filters, where I can choose different week-periods. The revenue of these week-periods are displayed/compared in a bar-chart.

What is my problem?

I need to be able to choose some other filters which are applied to both data bases (country, device, etc). To do so I created bridging tables, but it is not possible to create relationships between these bridging tables and my 2 data bases...

Is there any other possibility to compare different week-periods?

I know it is not possible, but I need something like - choose filter of week-measure ---> revenue of week-measure is displayed. 

[v-yuezhe-msft]

Hi @SimonGrams,

Could you please share sample data of your tales and post expected result here?

Also we need to know what is the error message you get when creating relationships between bridging tables and two databases.

Thanks,
Lydia Zhang

[SimonGrams]

Hi @v-yuezhe-msft

The data looks like this:

Relationships:

Desired Output:

---> Week-Filters are from the original and duplicated database and should be independent

---> Device, Country & Category Filters should influence both databases (field from bridging tables)

---> Viz should compare Revenue from original vs. duplicated database (week-period I vs week-period II)

The first relationship between a bridging table (here: LookUp_Country) and the 2 data bases works.

Error from trying to activate the other relationships (here: Data_Duplicate & LookUp_Category):

It's not possible to create a direct relationship between 'Data_Duplicate' and 'LookUp_Category', because this would lead to an ambiguousness between the tables 'LookUp_Category' and 'LookUp_Country'.

If I create another bridging table which includes all combinations there is the error of missing distinct values...

Edit: In this example I forgot the "Web"-Filter from the data bases - which should be a filter in the desired output as well (like country, category, device).

[MarkS · Accepted solution]

Hi Simon,

One way to get the output you are looking for would be to have a table with the distinct week numbers and relate to the week field on the Data table, set up as

and have two measures one for the current week numbers revenues:

current=CALCULATE(SUM(Table1[Revenue]),ALL(Comparable[CompareToWeek])) 

and the other measure for the Comparable week:

comparable=CALCULATE(SUM(Table1[Revenue]),ALL(Table1[Week]),USERELATIONSHIP(Table1[Week],Comparable[CompareToWeek]))

Then use these measures in the visualization

[SimonGrams]

Hi @MarkS,

awesome! Works perfect. Thank you very much