Applying Filters on different hierarchy levels

[rajat96]

Hello PBI Community, 

I am working on a problem statement that requires me to compare the sales varainces between two different periods. For example, I have to find the difference between the % of total sales for quarter 1 and % of total sales for May month end, to understand the difference in different categories of product. However,  data is defined in a maner wherein Quarter 1 can have only Jan, Feb and March.

Based on the selected month from the drop down, I want to calculate the above difference. However, I am unable to do the same when I have to subtract Quarter 1 results from that of May month end as they do not fall in the same hierarchies. 

@PowBi

[Anonymous · Accepted solution]

Hi @rajat96 ,

I suggest you to create two Date tables and then create two inactive relationship between them to Fact table.

Measure:

Sales % Period 1 = 
VAR _Period1 = TOTALYTD(SUM('Table'[Sales]),'Period 1'[Date],USERELATIONSHIP('Period 1'[Date],'Table'[Date]))
VAR _TotalYTD = TOTALYTD(SUM('Table'[Sales]),ALL('Period 1'[Date]),USERELATIONSHIP('Period 1'[Date],'Table'[Date]))
RETURN
DIVIDE(_Period1,_TotalYTD)

Sales % Period 2 = 
VAR _Period1 = TOTALYTD(SUM('Table'[Sales]),'Period 1'[Date],USERELATIONSHIP('Period 2'[Date],'Table'[Date]))
VAR _TotalYTD = TOTALYTD(SUM('Table'[Sales]),ALL('Period 2'[Date]),USERELATIONSHIP('Period 2'[Date],'Table'[Date]))
RETURN
DIVIDE(_Period1,_TotalYTD)

Diff = [Sales % Period 1] - [Sales % Period 2]

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.