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