Calculating the percentile for past X days, minus one

[vbaker]

I am attempting to run PERCENTILE.INC on series of data to get 0.25, 0.50, and 0.75 for a date range.  If I do =PERCENTILE.INC(TABLE[Amount],0.5) for the past 5 days (1/6-1/10), it works. Essentially, I am wanting to look at 1/5-1/9 and 1/6-1/10 on the same line. 

However, if I do today minus 1 to get the information for yesterday and then filter by group, it doesnt work.  I am using this calculation to do the PERCENTILE.INC 0.5 for yesterday

=CALCULATE(PERCENTILE.INC(TABLE_NAME[Amount],0.5),DATESBETWEEN(TABLE[DATE],LASTDATE(TABLE[DATE])-1, LASTDATE(TABLE[DATE])-1)).

Date      Amount          Group

1/1            1000                A

1/1            3000                B

1/2            1500                A

1/2            5000                B

1/3            2000                A

1/3            1000                B

1/4            2500                A

1/4            1500                B

1/5            1500                A

1/5            3500                B

1/6            1250                A

1/6            2250                B

1/7            1000                A

1/7            3000                B

1/8            1000                A

1/8            2000                B

1/9            2000                A

1/9            3000                B

1/10          2500                A

1/10          3500                B

[v-yuta-msft · Accepted solution]

Hi vbaker,

To achieve your requirement, you can use DAX:

PERCENTILE.INC 0.5 for yesterday = CALCULATE(PERCENTILE.INC(TABLE_NAME[Amount], 0.5) , DATESBETWEEN(TABLE_NAME[Date], LASTDATE(TABLE_NAME[Date]) - 6, LASTDATE(TABLE_NAME[Date]) - 1))

The result is like below and you can refer to PBIX:

https://www.dropbox.com/s/wzv8sp7op5p6pbw/For%20vbaker.pbix?dl=0

Regards,

Jimmy Tao

[vbaker]

How do I address LASTDATE(TABLE_NAME[Date]) - 6, LASTDATE(TABLE_NAME[Date]) - 1 if I want a date filter to be what derives the date range?