Comparing 2 unrelated date periods

[DeanJ]

Not sure if this belongs here or in the DAX forum, but I am trying to create a report with 2 date slicers that are not related to each other. Date Table 1 has a one to many relationship with the Fact table. Date Table 2 has an inactive 1 to many relationship with the date table. I have 2 questions:

1. Is this the recommended approach?
2. If so, how would I calculate using the inactive relationship. I’ve tried various permutations of the DAX below:

Compare Amount =
CALCULATE (
[Total Amount],
ALLSELECTED (DimDateCompare[Year Month Name]), USERELATIONSHIP(DimDateCompare[Date], FactPLData[GLDate])
)

[DeanJ]

Example file attached
Multiple Date Comparison.pbix (2.9 MB)

[AntrikshSharma]

Follow these steps:

[DeanJ]

@AntrikshSharma Thank you for your response. I really appreciate it! That does work to show the measure in a visual by itself. What I am trying to do is to show both measures in a matrix and also do calculations with both measures such as difference between them. I’ve attached an updated example. Multiple Date Comparison v2.pbix (2.9 MB)

[DeanJ]

One possible solution might be to use 2 fact tables then do a 1 to many active relationship between date table1 and fact table1 and a separate 1 to many active relationship between date table 2 and fact table 2.

The fact table is a good size, so another way would be preferable if possible.

[DeanJ]

I’m also thinking of how to use SUMX and ALL to remove the filtering of the first date slicer when calculating the comparison period amount.

[AntrikshSharma]

@DeanJ Like this?

Compare Period Amount =
CALCULATE (
    [Total Amount],
    ALL ( FactData[Group] ),
    TREATAS ( VALUES ( DimDateCompare[Date] ), DimDate[Date] )
)

Multiple Date Comparison v2.pbix (2.9 MB)

[DeanJ]

@ AntrikshSharma Dude, you are amazing! Thank youI

[AntrikshSharma]

@DeanJ