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.

     

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

     

DataInsights's avatar
DataInsights
Super User
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].

 

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

 

 

  • katrine_d2o's avatar
    katrine_d2o
    Helper I
    4 years ago

    This is great, thank you. I will test it with the comparison date table I have. Will let you know how it works out.

    • katrine_d2o's avatar
      katrine_d2o
      Helper I
      4 years ago

      Hello again DataInsights ,

      I am working with a team in vietnam who has been off for a couple of weeks due to the tet new year, so still waiting for them to update the dataset. Very cumbersome

       

      Anyways, the solution above shows me how to use a disconnected table with -x periods. But, what if I have a disconnected date table, how can I choose a previous period?

      Situation is the same as above, I want to compare Jan-22 with a random previous period, but not by choosing -x months, but by choosing Mar-19, or Jul-20. What I would like is to have a slicer for the starting month, which is connected as usual. Then I want a slicer for the disconnected table that will display the period I am comparing with and the related results...and of course the differences.

       

      Thanks again

      katrine_d2o

      • DataInsights's avatar
        DataInsights
        Super User
        4 years ago

        katrine_d2o,

         

        So in addition to the two slicers (Date and Comparison Month), you want a third slicer with a disconnected date table? This would give you the option of specifying a Date (main date table), and then using either the Comparison Month slicer or disconnected date table slicer to specify the comparison month, right?