Forum Discussion

rbbi's avatar
rbbi
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Sum with DatesInPeriod not working?

Hi, I'm trying some simple DAX but getting a strange result .. trying to sum the last month's data from each date. Also summing the next month's data but this is working OK.   Here is the data......
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi  rbbi,

     

    If you want to use DATESINPERIOD function, you'd better create a calendar table.

     

    Such as := CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]),DATESINPERIOD(DateTime[DateKey],DATE(2007,08,24),-21,day))

     

    In the case of not creating a calendar, use filter to determine the specific time range is better.

     

    In addition, I'm a little confused about your logic, if it is convenient could you share your expected output, so that we can help further investigate on it? 

     

    Best Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi  rbbi,

 

If you want to use DATESINPERIOD function, you'd better create a calendar table.

 

Such as := CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]),DATESINPERIOD(DateTime[DateKey],DATE(2007,08,24),-21,day))

 

In the case of not creating a calendar, use filter to determine the specific time range is better.

 

In addition, I'm a little confused about your logic, if it is convenient could you share your expected output, so that we can help further investigate on it? 

 

Best Regards,

Cherry

  • rbbi's avatar
    rbbi
    Icon for Advocate II rankAdvocate II
    8 years ago

    thanks v-piga-msft,

     

    I had some more time to experiment today and found the same thing .. I added a calendar table with CALENDARAUTO() (easy and quick for this test, but in real life I use a M-based calendar table) and linked to my fact table in a relationship and that fixed the totals.

    It's interesting to me that the DATESINPERIOD function still produces results without a calendar table (or at least without consecutive dates in the fact table), with those results being sometimes correct and sometimes not.

     

    To answer your question, I'm trying to get my head around DAX and time intelligence functions & measures. This example is just a simplified version, and it uses SUM, calculating a total for the past month up to the date of each record. The month-ahead measure was just an attempt to validate the results and understand what's going on. I'm aiming ultimately to have a 1 month moving average as well as year-to-date and month-to-date. I was just using SUM because it's easier to verify the results when looking at them.