Displaying the same data under another table - using USERELATIONSHUP() maybe?

[KaiRado]

Hi,

I have a table Sales that is linked to two date tables Date1 and Date2. Let's assume that the data looks like this:

Now I need the measure Sales to display the same data as on the second table while using the date table Date1:

I tried to use USERELATIONSHIP() but it doesn't work. Maybe I was using it wrongly.

Do you have any tips or ideas?

Thank you in advance!

[tamerj1]

Hi @KaiRado 
Try using TREATAS

Sales =
CALCULATE (
    SUM ( Sales[Sales] ),
    TREATAS ( VALUES ( Date1[Date] ), Date2[Date] )
)

[KaiRado]

Hi @tamerj1 !  Unfortunately that didn't work.

But thank you for your time!

[tamerj1]

@KaiRado 

Seems I wrote it the other way around. Please try

Sales =
CALCULATE (
    SUM ( Sales[Sales] ),
    TREATAS ( VALUES ( Date2[Date] ), Date1[Date] )
)

[KaiRado]

Hi @tamerj1 , I've tried both ways and I'm getting odd numbers.

Thank you

[tamerj1]

@KaiRado 

Sorry I just saw your reply. Please try with REMOVEFILTERS

Sales =
CALCULATE (
    SUM ( Sales[Sales] ),
    TREATAS ( VALUES ( Date2[Date] ), Date1[Date] ),
    REMOVEFILTERS ( Date1 )
)

[KaiRado]

Hi @tamerj1 , unfortunately I'm getting the same result as in the first table with Date1.
I'm not sure if it is even possible to achieve the resut I want. 

Thank you so much for your time!

[tamerj1 · Accepted solution]

Hi @KaiRado 
Please refer to attached sample file with the solution

Sales Date2 = 
SUMX ( 
    VALUES ( 'Date 1'[Month] ),
    CALCULATE (
        VAR CurrentMonth = MAX ( 'Date 1'[Month Number] )
        VAR Dates = FILTER ( VALUES ( 'Date 2'[Date] ), MONTH ( 'Date 2'[Date] ) = CurrentMonth )
        VAR Result =
            CALCULATE ( 
                [Sales Amount],
                TREATAS ( Dates, Sales[Order Date] ),
                REMOVEFILTERS ( 'Date 1' )
            )
        RETURN
            Result
    )
)

[KaiRado]

Hi @tamerj1 , thank you very much!