Problem Filtering Dates

[Anonymous]

Hello,

I am trying to do a calculation over a 1 year period then limit that calculation or extend that calculation to a certain time period

CALCULATE(

[Daily Average Sales],ALL('Calendar'[Date].[Date]), DATESBETWEEN('Calendar'[Date].[Date],DATE(2017,8,1),DATE(2018,3,31)),

DATESBETWEEN(

'Calendar'[Date],

LASTDATE('Calendar'[Date])-365,

LASTDATE('Calendar'[Date])))

The above calculation is correctly beginning the function at the right date but it is continuing to do the calculation after the end date and I don't know why. I tried doing the same as above with only a 90 day period and the function worked. Any help would be greatly appreciated. Thanks.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Here is a set of dummy data with a demo of what I am trying to do. The last column is what I am trying to create in BI, but I want to do this calculation using different filter contexts changing the # of "widgets sold" for non 1 year periods back through time:

Widgets	Dates	Rolling Average Previous 12 Months	Rolling Average Month 13-24	Growth Between Two Rolling Averages
146	Jan-16	N/a	N/a	N/a
322	Feb-16	N/a	N/a	N/a
131	Mar-16	N/a	N/a	N/a
335	Apr-16	N/a	N/a	N/a
258	May-16	N/a	N/a	N/a
180	Jun-16	N/a	N/a	N/a
110	Jul-16	N/a	N/a	N/a
345	Aug-16	N/a	N/a	N/a
385	Sep-16	N/a	N/a	N/a
135	Oct-16	N/a	N/a	N/a
250	Nov-16	236.0909091	N/a	N/a
418	Dec-16	251.25	N/a	N/a
267	Jan-17	261.3333333	N/a	N/a
19	Feb-17	236.0833333	N/a	N/a
300	Mar-17	250.1666667	N/a	N/a
252	Apr-17	243.25	N/a	N/a
264	May-17	243.75	N/a	N/a
123	Jun-17	239	N/a	N/a
94	Jul-17	237.6666667	N/a	N/a
1	Aug-17	209	N/a	N/a
294	Sep-17	201.4166667	N/a	N/a
476	Oct-17	229.8333333	N/a	N/a
373	Nov-17	240.0833333	236.0909091	2%
146	Dec-17	217.4166667	251.25	-13%
385	Jan-18	227.25	261.3333333	-13%
21	Feb-18	227.4166667	236.0833333	-4%
11	Mar-18	203.3333333	250.1666667	-19%
219	Apr-18	200.5833333	243.25	-18%
88	May-18	185.9166667	243.75	-24%
7	Jun-18	176.25	239	-26%
297	Jul-18	193.1666667	237.6666667	-19%
363	Aug-18	223.3333333	209	7%
18	Sep-18	200.3333333	201.4166667	-1%
396	Oct-18	193.6666667	229.8333333	-16%
242	Nov-18	182.75	240.0833333	-24%
350	Dec-18	199.75	217.4166667	-8%
341	Jan-19	196.0833333	227.25	-14%
65	Feb-19	199.75	227.4166667	-12%
491	Mar-19	239.75	203.3333333	18%
442	Apr-19	258.3333333	200.5833333	29%
8	May-19	251.6666667	185.9166667	35%
156	Jun-19	264.0833333	176.25	50%
306	Jul-19	264.8333333	193.1666667	37%
243	Aug-19	254.8333333	223.3333333	14%

[Ashish_Mathur]

Hi,

The third colum there seems like a measure.  Can you share the raw data.

[v-yuta-msft]

Hi datakid1230,

Remove the all statement and try again:

Result =
CALCULATE (
    [Daily Average Sales],
    DATESBETWEEN (
        'Calendar'[Date].[Date],
        DATE ( 2017, 8, 1 ),
        DATE ( 2018, 3, 31 )
    ),
    DATESBETWEEN (
        'Calendar'[Date],
        LASTDATE ( 'Calendar'[Date] ) - 365,
        LASTDATE ( 'Calendar'[Date] )
    )
)

Regards,

Jimmy Tao

[AlB]

Hi @Anonymous

Can you share the pbix? It would be much easier to see what is going on 

[Anonymous]

No I can't share the information sorry. No worries if there is not enough information to provide an answer.