Sum with DatesInPeriod not working?

[rbbi]

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...

Date,Value
2017-12-25,2
2018-01-10,3 <- - Sum of month AFTER this is correctly showing as 26
2018-01-17,7 <
2018-01-20,1 <
2018-02-01,6 <
2018-02-04,9 <- Sum of month PRIOR to this should be  9+6+1+7 + 3 = 26, but shows as 15 ???
2018-04-16,2
2018-04-17,3
2018-05-04,9
2018-05-11,5
2018-05-28,2
2018-06-30,1

Here's a table visual, dates as dates not hierarchy, values NOT aggredated ...

Here is the M that creates this ...

let
Source = #table(
{"Date", "Value"},
{
{#date(2017,12,25),2},
{#date(2018,01,10),3},
{#date(2018,01,17),7},
{#date(2018,01,20),1},
{#date(2018,02,01),6},
{#date(2018,02,04),9},
{#date(2018,04,16),2},
{#date(2018,04,17),3},
{#date(2018,05,04),9},
{#date(2018,05,11),5},
{#date(2018,05,28),2},
{#date(2018,06,30),1}
}
),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}})
in
#"Changed Type"

Here are my measures:

Sum Minus One Month = CALCULATE(SUM(T[Value]),ALL(T),DATESINPERIOD(T[Date],MAX(T[Date]),-1,MONTH))

Sum Plus One Month = CALCULATE(SUM(T[Value]),ALL(T),DATESINPERIOD(T[Date],MAX(T[Date]),1,MONTH))

The "Minus One" measure for 4 Feb seems to be summing only to the two Feb dates and ignoring the January dates, even though they are within one month prior!?

Can anyone help with this?

thanks!

[v-piga-msft · Accepted solution]

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]

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.