cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rbbi Regular Visitor
Regular Visitor

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

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

 

DatesInPeriod.jpg

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sum with DatesInPeriod not working?

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Sum with DatesInPeriod not working?

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rbbi Regular Visitor
Regular Visitor

Re: Sum with DatesInPeriod not working?

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.

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 43 members 1,018 guests
Please welcome our newest community members: