Comparing 2 unrelated date periods

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

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

Follow these steps:

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

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.

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.

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

3 Likes

@ AntrikshSharma Dude, you are amazing! Thank youI

ty

2 Likes

@DeanJ

2 Likes