DATESBETWEEN and LOOKUPVALUE combined

[Paul911]

I've searched quite extensively and I believe it is down to a syntax error but I need held with the DATESBETWEEN DAX. 

I have a Column return a count (i.e. "1") if the DATESBETWEEN are from a lookupvalue X (the release date) to lookup y (release date + X time). I have having difficulty with the latter part. There are two tables 1) Table 1 and 2) Summary. See below - 

Column After 3 Mo. =

CALCULATE(COUNT(Table1[DefectID]),

DATESBETWEEN(Table1[DateReported], LOOKUPVALUE(Summary[Rel. Date], Summary[Program], Table1[ProductFamilyGroup]), [--> [This is the "End Date for the DATESBETWEEN DAX] DATEADD(???)

Do I need to add in the LOOKUPVALUE again? 

[sturlaws]

Hi, @Paul911 

you could try writing your formula like this:

Column After 3 Mo. =
VAR _startDate =
    LOOKUPVALUE ( Summary[Rel. Date], Summary[Program], Table1[ProductFamilyGroup] )
VAR _numberOfMonths = 3
VAR _endDate =
    DATE ( YEAR ( _startDate ), MONTH ( _startDate ) + _numberOfMonths, DAY ( _startDate ) )
RETURN
    CALCULATE (
        COUNT ( Table1[DefectID] ),
        DATESBETWEEN ( Table1[DateReported], _startDate, _endDate )
    )

Since you have not added any sample data in your post, I have not checked if this works.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Paul911]

Hi @sturlaws 

Thanks for the prompt feedback. Was hoping to avoid creating VARs but intuitively, that makes sense. 

I did encounter an argument where function 'DATE' has the wrong data type. Both [Rel. Date] and [Date Reported] are formatted as type Date. Any pointers here without assessing the sample data? I'll also check the forums.  

Regards, 

Paul

[sturlaws · Accepted solution]

no reason to be afraid of variables, in this case they are just there to make your code more readable. You can write this code without the variables, just replace the variables with the expressions in the variables.

When it comes to dates not recognized as dates, despite being formated, I have come across a couple these situations lately. A workaround is to create a new column like this newDate=date(year(oldDate);month(oldDate);day(oldDate)), and replace oldDate with newDate in your DAX-code

[Paul911]

@sturlaws That works. So does wrapping the overall formula with IFERROR. Is this due to leap year? In any case, thanks for your help.