Forum Discussion

Bustardo's avatar
Bustardo
Helper I
9 years ago
Solved

Dax Question re: Datesbetween

I have a question as to why these measures are giving me different results  because it does not make sense to me.   I have a Table, we'll call products. There is a column called ActualDate. The Min...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    Hmm. I cannot seem to replicate this. That being said, the syntax seems weird to me. My suspician is that there is something wonky going on in your relationship between the tables. For example, I was able to replicate this when the things that I was counting did not have matching relationships in the dimDate table. So, I might have 8 items, but I only had 6 dates in my dimDate table. So, do all of your ActualDates match up with all of the dates in your dimDate table?

     

    Would be interested if you could post some sample data in a table that could be used to replicate this issue. 

Bustardo's avatar
Bustardo
Helper I
9 years ago
From the first one, without dates, I'm getting about 300,000 results. When i use the datesbetween its going down to 34,000 which doesn't make sense to me because all rows are between those dates.
Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Hmm. I cannot seem to replicate this. That being said, the syntax seems weird to me. My suspician is that there is something wonky going on in your relationship between the tables. For example, I was able to replicate this when the things that I was counting did not have matching relationships in the dimDate table. So, I might have 8 items, but I only had 6 dates in my dimDate table. So, do all of your ActualDates match up with all of the dates in your dimDate table?

 

Would be interested if you could post some sample data in a table that could be used to replicate this issue. 

  • Bustardo's avatar
    Bustardo
    Helper I
    9 years ago

    I appreciate your help. How would I get you that large table? it's approx 300,000 rows.

    I just verified that no dates are missing from my date table by dumping them all in excel and checking for non matching values.

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      9 years ago

      Just a small sample should suffice. That being said, it could be some sort of problem only at scale. You could also share out your PBIX file on OneDrive or something similar if it is not sensitive data.

      • Bustardo's avatar
        Bustardo
        Helper I
        9 years ago

        I tried to simplify the problem by only grabbing the relevant columns to ensure nothing else is screwing with the results.

         

        So now I have 3 measures:

         

        productCount = Calculate(COUNTROWS(Products))   Result : 347670

Productcount greater than 7/1/2016 = Calculate(countrows(products), Products[actualdate] >= Date(2016,7,1) && Products[actualdate] <= Date(2017,6,30))    Result : 347670 (as expected)

Product count with Date = Calculate(countrows(Products),Datesbetween(DimDate[Date],Date(2016,7,1),Date(2017,6,30)))   Result 67584

        I'll see if I can narrow the dataset for you because this is making my head explode.