Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.