Periods Comparison

[Yousra-ali]

Hello Dears,

i have created table date and dublictae it and call it comparison, i have linked it to the main sales table i have, so now i have 2 slicer one is current period and the other one is past period with a matrix table have the type of the product as a coulomn and the ttl sales of current period and the past period, so it will look like belwo.

then i have created a measure 

P.P TTL SAL =

CALCULATE(

    [Total Sales Measure],

    ALL('23-24'[Calendar Date]),

    USERELATIONSHIP('23-24'[Calendar Date], 'Comparison'[Date]))

 

to keep the first slicer not effcting the sales of the past period in the matrix table.

i hope it was clear, kindly let me know if you needed more info to help.

[danextian · Accepted solution]

Hi @Yousra-ali 

If the goal is to compare to periods controlled by different slicers, use a second dates/calendar table that has an active relationship to your fact table. Invoke the inactive relationship in a table using USERELATIONSHIP. You also need to remove the filter coming from the dates table with an active relationship to fact

Total Revenue Comparison = 
CALCULATE (
    [Total Revenue],
    USERELATIONSHIP ( ComparisonDates[Date], Data[Date] ), 
    REMOVEFILTERS ( Dates )
)

Please see the attached pbix.

[Yousra-ali]

Thank you so much @danextian 

[Cookistador]

Hello @Yousra-ali 

As you have two sclicers, the best thing to do is to disconnected your second date dimension and in the measure used something like:

P.P TTL SAL =

VAR SelectedDate = SELECTEDVALUE('Date'[Date],blank())

RETURN

CALCULATE(
   [YourMeasure],
ALL(...)

    'Table'[Date] = SelectedDate)
If you need more help to set up the measure, do not hesistate to share a dummy dataset 🙂