Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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 ...
DAX
USERELATIONSHIP
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    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
    )
)

     

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi Anonymous 
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
    )
)

 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi tamerj1 , thank you very much!