How to calculate actual sales with using dax language instead of in excel?

[ommfin123]

Hi all. Wish you a good day!

I would like to add a column to calculate the total of actual sales in my Target table. (by month & year as the Target table)

Current formula I am using as below:

Actual Sales = CALCULATE(SUM('Sales Report'[Total]),

FILTER('Sales Report', 'Sales Report'[Estimated Delivery Date]='SalesPlanning'[DATES]))

But what I want is date according to the table.

The results I got:

Appreciates lot for your help!

[tamerj1]

Hi @ommfin123 
Please try

Actual Sales =
CALCULATE (
    SUM ( 'Sales Report'[Total] ),
    FILTER (
        ALL ( 'Sales Report' ),
        'Sales Report'[Estimated Delivery Date] = RELATED ( 'SalesPlanning'[DATES] )
    )
)

[ommfin123]

Dear @tamerj1 . Thank you for your help!

The formula is with no error but the results comes out were the total sales amount of the whole table.

I would like it to filter according to the SalesPlanning's monthyear date, may I know how should I do?

Thank you so much.

[tamerj1]

@ommfin123 

Please try

Actual Sales =
CALCULATE (
SUM ( 'Sales Report'[Total] ),
FILTER (
CALCULATETABLE ( 'Sales Report', ALL ( 'Sales Report' ) ),
'Sales Report'[Estimated Delivery Date] = RELATED ( 'SalesPlanning'[DATES] )
)
)

[ommfin123]

Hi @tamerj1 . Thank you for your warm help.

Unfortunatelt it still can't get it :'(

[ommfin123]

Or maybe is my tables' relationship linked wrongly?

I'm not sure too.