Forum Discussion

Yousra-ali's avatar
Yousra-ali
New Member
1 year ago
Solved

Periods Comparison

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.
Need Help
Tutorial Requests
  • danextian's avatar
    danextian
    1 year ago

    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.

3 Replies

  • Cookistador's avatar
    Cookistador
    Icon for Super User rankSuper User
    1 year ago

    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 🙂
  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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.