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 nights, hours, hours per room night, revenue etc. - that is fine. I want to compare the result with a previous period and show the difference. I want the comparison period to be dynamic - ie. I want to choose to compare with 1 month ago, 3 months ago, 15 months ago etc. I started with creating dateadd for -1, -2, and -3 months, and made a page with each of the results, but I cannot create this for every month back to 2018. 

 

Is there a way to use a slicer to choose the comparison period and calculate the difference? I can always create a list of results for a different period, but I want to see the difference too. I have used multi-row cards here.

 

 

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

     

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

     

6 Replies

  • 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