Create sales measure based on date table

[Anonymous]

Hey everyone, 

I tried to look up in the community but haven't found anything this specific.

I have two tables: 

1. Sales Table- including 2 columns: Full date & Amount[USD]

2. Date Table - including 4 columns: full date, the day of the week, FY, Quarter. 

I am trying to create a total revenues measure based on the filtered date-table in a way that when I use slicer (for example a specific month) it will calculate other measures based on the total revenues one.

I tried using this formula:

New_Total_Revenues = CALCULATE(

SUM('Sales'[Amount]),

FILTER(ALL('Date Dim'[Date]),

DATESBETWEEN('Date Dim'[Date],FIRSTDATE('Date Dim'[Date]),LASTDATE('Date Dim'[Date]))))

However, I get the following error:

"MdxScript(Model) (162,14) Calculation error in measure: A table of multiple values was supplied where a single value was expected"

I am having a tough time with this. I am trying to create a dynamic total revenue measure since later on I will use it to create  12/6/3 months sales averages and FYTD average measures which I would like to be able to use slicer to get dynamic and accurate results when I choose any month.

Thanks in advance for the help, and if I am missing something here and a normal total revenue measure such as SUM('Sales'[Amount]) still will give me the same result then I would like to know why, because if I choose a certain month all of the date-table will be decreased to the specific month and I am afraid it will exclude sales which need to be included.

All the best!

[parry2k]

@Anonymous this is the fix but I think you are over complicating things.

New_Total_Revenues = CALCULATE(
SUM('Sales'[Amount]),
DATESBETWEEN('Date Dim'[Date],FIRSTDATE('Date Dim'[Date]),LASTDATE('Date Dim'[Date]) )
)