Next 7 days , 14 days , 21 days

[Anonymous]

Hi All,

Below is my data.

I want to find out what what all "open" Status for next 7 days , 14 days and 21 days from <hard_coded_date>

[amitchandak]

@Anonymous 

From the date in the slicer

next 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),7,Day))  
next 14 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),14,Day)) 
next 21 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),21,Day)) 

Last 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-7,Day)) 

[Anonymous]

Hi , 

Thanks for your rpl !!

I dont have a sales column, I have status coloum. I need to know which all the ID that status are open for next 7 days 

[amitchandak]

Can you share sample data and sample output.

[Anonymous]

neData is alreday share.

Next 7 days (includes today).

Date Column is today.. its fine if I dint get date. I want to display ID numbers which are Open for next 7 days

ID	Date	Due_Date	Status
ABCD123	04/06/2020	04/06/2020	Open
aaaa	04/06/2020	05/06/2020	Open
ccc	04/06/2020	06/06/2020	Open
cvdfc	04/06/2020	07/06/2020	Open
aafaf	04/06/2020	08/06/2020	Open

[V-lianl-msft]

Hi @Anonymous ,

Create a measure like this and use it as visual level filter:

Measure = 
var _date = IF(ISBLANK(MAX('table'[Date])),TODAY(),MAX('table'[Date]))
return IF(MAX('table'[Status])="open"&&_date<=TODAY()+7&&MAX('table'[Due_Date])>=TODAY()+7,1)

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi ,

Thanks for your rpl.

I am getting the error.

[V-lianl-msft]

Hi @Anonymous ,

Could you share the sample pbix via cloud service like onedrive for business?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Please share your gmail id

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

According to the data you provided, I got the expected result.

I mean drag measure to visual level filter, not to table chart.

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.