An argument of function date has the wrong data type or the result is too large or too small

[Eduardo6]

I would like to only calculate the sum of values after a certain date. I do not want to have to manually key in the date every month as new data comes in. I have created a Measure to isolate the latest data point, however the target date I'm after is always 3 months prior to the maximum posting date of the data. Sorry for the huge amount of detail. I have spent alot of time on this both trying things out and reading other similar issues. 

1. Pull the max posting date. This Measure works fine!

Date Latest Actuals=

MAX('Actuals'[PostingDate])

2. Isolate the variables to be input into the target date measure

Date Year=

Year([Date Latest Actuals])

Date Month=

Month([Date Latest Actuals])

Date Year=

Day([Date Latest Actuals])

3. Combine the measures above to create the Target Date, this works fine when I pull it into a card.

DateTarget=

DATE([Date Last Year],[Date Last Month]-3,[Date Last Day])

4. Use the Target date in a calculation, This is where it breaks down.

SalesFcst= Calculate(sum('Fcst'[Sales]), FILTER('Date','Date'[DATE]>[DateTarget]))

I've also tried

SalesFcst= Calculate(sum('Fcst'[Sales]), FILTER('Fcst','Fcst'[Date]>[DateTarget]))

Below works but this is my original measure that is not 3 months prior, so no use, but I wanted to make sure a measure could be used in this calculation.

SalesFcst= Calculate(sum('Fcst'[Sales]), FILTER('Fcst','Fcst'[Date]>[Date Latest Actuals]))

As you can tell from the measures above I have 3 sets of dates (Date Table, Fcst Table has dates, Actuals Table has dates)

Date table was built using 

Date= CALENDAR(DATE(2014,1,1),DATE(2023,12,31))

Fcst Table has dates from 1/1/2019-12/1/2021 in month/day/year format

Actuals Table has dates from 1/1/2016-6/1/2020

If you made it all the way down here, thank you in advance for your time!

[Greg_Deckler]

@Eduardo6 - I suppose the first thing to check would be to make sure that your measure is set to be a Date data type and not text for example. Second thing I would check would be to have SalesFcst measure just return Date Latest Actuals so that you can check what the value of that is in context of where you are using it.

[Eduardo6]

@Greg_Deckler  the measure is set to be a Date. When I change the SalesFcst measure to just return the Latest Actuals date it returns the following

Year	Month	SalesFcst
2018	June	6/1/2018
2018	July	7/1/2018
2018	August	8/1/2018
2018	September	9/1/2018
2018	October	10/1/2018

[Greg_Deckler]

@Eduardo6 - Any chance that there is some value in 'Fcst'[Date] that is like blank or something like that?

[Eduardo6]

@Greg_Deckler - No blanks in that date column. 

[Greg_Deckler]

Well, there may not be, but is that true within the context of the visualization? That's the key with measures.

[Eduardo6]

@Greg_Deckler 

Within the context of the visualization there would be blanks. The visualization is just a table, My dates column pulls from my date table (parameters below) and my FcstTable dates do not span as long. But this is the case with my Actuals Table as well and when I use the measure I created from that table it works fine. 

Date= CALENDAR(DATE(2014,1,1),DATE(2023,12,31))

Fcst Table has dates from 1/1/2019-12/1/2021 in month/day/year format