Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATESBETWEEN and LOOKUPVALUE combined

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? 
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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

4 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi, Anonymous 

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        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