Result difference between dynamic periods

[katrine_d2o]

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 · Accepted solution]

@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 · Accepted solution]

@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]

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]

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]

@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]

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 · Accepted solution]

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

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