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

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?

    • 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.

         

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