Forum Discussion

katrine_d2o's avatar
katrine_d2o
Helper I
4 years ago
Solved

Result difference between dynamic periods

Hello,   I have been searching videos and communities without any luck - or perhaps I am just not understanding something.   I am comparing a  number of different measures for hotels, like room n...
  • DataInsights's avatar
    DataInsights
    4 years ago

    katrine_d2o,

     

    You can achieve this with a disconnected table (no relationships). In this example, the table is ComparisonMonth:

     

     

    FactTable (m/d/yy format):

     

     

    Data model:

     

     

    Measures:

     

    Revenue Total = SUM ( FactTable[Revenue] )
    Revenue Comparison = 
VAR vComparisonMonth =
    SELECTEDVALUE ( ComparisonMonth[Month] )
VAR vResult =
    CALCULATE (
        [Revenue Total],
        DATEADD ( DimDate[Date], vComparisonMonth, MONTH )
    )
RETURN
    vResult
    Revenue Variance = [Revenue Total] - [Revenue Comparison]

     

    Date slicer uses DimDate[Date]. Comparison Month slicer uses ComparisonMonth[Month].

     

    ------------------------------------------------------------

     

     

  • DataInsights's avatar
    DataInsights
    4 years ago

    katrine_d2o,

     

    Yes, you need a disconnected date table. You can create one using a calculated table:

     

    ComparisonDate = DimDate

     

    Change the Revenue Comparison measure to the following. The TREATAS function changes the lineage of the disconnected date table (ComparisonDate) to the main date table (DimDate).

     

    Revenue Comparison = 
CALCULATE (
    [Revenue Total],
    TREATAS ( VALUES ( ComparisonDate[Month Year] ), DimDate[Month Year] )
)

     

    Slicer interactions should be enabled.

     

    -------------------------------------------------------------

     

katrine_d2o's avatar
katrine_d2o
Helper I
4 years ago

Hi DataInsights 

Thank you for your prompt reply.

I would like only two slicers; the orignal date and comparison date. So, instead of a slicer where I chose -1, -2, -3 etc., I can pick Dec-2021, Jul-2020, Apr-2019. From what I understand, I will need a disconnected date table for this?

DataInsights's avatar
DataInsights
Super User
4 years ago

katrine_d2o,

 

Yes, you need a disconnected date table. You can create one using a calculated table:

 

ComparisonDate = DimDate

 

Change the Revenue Comparison measure to the following. The TREATAS function changes the lineage of the disconnected date table (ComparisonDate) to the main date table (DimDate).

 

Revenue Comparison = 
CALCULATE (
    [Revenue Total],
    TREATAS ( VALUES ( ComparisonDate[Month Year] ), DimDate[Month Year] )
)

 

Slicer interactions should be enabled.

 

-------------------------------------------------------------