Sum up sales in the latest n days based on sales channel

[cmc910514]

Hi people, 
I need your help. I have 3 tables and their relationships as the picture below.

I need to sum up the sales value according to different channel:
Channel A: always sum up sales fromlatest 2 days
Channel B: always sum up sales from latest 2 days
Channel C: always sum up sales from latest day

Do you have any ideas on how I should write the DAX codes? Thanks....

[tamerj1]

Hi @cmc910514 
Please try

Top N Sales =
SUMX (
    VALUES ( TableA[Channel] ),
    VAR N =
        IF ( TableA[Channel] = C, 1, 2 )
    VAR T =
        TOPN ( N, CALCULATETABLE ( TableC ), TableC[SalesDate] )
    RETURN
        SUMX ( T, TableC[Value] )
)

[cmc910514]

Hi @tamerj1 , the outcome turns out not correct...but thanks still.

[tamerj1]

@cmc910514 
Would you please paste some screenshots of your visual along with the DAX that you have used?

[cmc910514]

Hi @tamerj1 , just realized that I should revise the table C as below. The sales frequency between different channels is different. Really thanks for your help! 

[tamerj1]

@cmc910514 
Anyway, please try

Top N Sales =
SUMX (
    VALUES ( TableA[Channel] ),
    VAR N =
        IF ( TableA[Channel] = C, 1, 2 )
    VAR T =
        TOPN (
            N,
            CALCULATETABLE ( VALUES ( TableC[Sales Date] ), ALL ( TableC[Sales Date] ) ),
            TableC[SalesDate]
        )
    RETURN
        CALCULATE ( SUM ( TableC[Value] ), KEEPFILTERS ( TableC[Sales Date] IN T ) )
)

[cmc910514]

Hi @tamerj1 , now it's correct for channel A & B, but C doesn't show up...

[tamerj1]

@cmc910514 

I'm not what could be missing. Please share the sample file. I'll work on it tomorrow morning as I don't have access now to my PC. Hopefully, you'll find the solution before. 

[cmc910514]

Hi @tamerj1 please try to download the file through the link below. Hope it works...

Top N Sales 

[tamerj1]

@cmc910514 

That should make no difference. Would you please help me out and share the results that you have obtained using my measure?