How to get data to till current month and future months with conditional column

[harib]

Hi All, 

I would like to create a slicer with "Future Date","Till Date"  values for date field.

I have a date filed  like below (MM/DD/YYYY)

Date

01/31/2020

02/29/2020

03/31/2020

04/30/2020

05/31/2020

06/30/2020

07/31/2020

My requirment is,  if user want to see data  offuture date they can select "Future Date" option else if user wants to see the data to till month(05/31/2020) then they can select "Till Date" option in slicer.

So i have create formula like this . Future Period= IF('Date'[Date]<=TODAY(), "Future Date","Till Date")

With above formula  I'm able to get only data till 04/30/2020, It is not showing 05/31/2020 data. I would like to get this month (05/31/2020) also.

Future Period= IF('Date'[Date]<=TODAY()+31, "Future Date","Till Date")

If i add +31 then it's showing data to till month(05/31/2020), but i think this is not the correct way.

Can anyone please let me know how to get it.

Thanks 

[v-easonf-msft]

Hi , @harib 

If  today is  (05/11/2020) ,what you want is to show data to the last date of this month(05/31/2020) , right? 

If so ,you can think of it(last day of this month) as the first day of the next month minus one day .

Try calculated column as below:

Period Type 3 = 
var last_date_of_this_month=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1
return
IF (Sheet2[Date-New] > last_date_of_this_month, "Future", "Till Date" )

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harib]

@v-easonf-msft 

Excellent. Issue resolved. 😊

Thanks a lot

[parry2k]

@harib bit confused isn't future date will be anything after today so your formula would be

Period Type = IF ( 'Date'[Date] > TODAY(), "Future", "Till Date" )

if it doesn't work, share sample data and what results you are getting and what is wrong, explain the problem with data.

[harib]

@parry2k 

I have tried the formula which u have provided, it is also showing the result to 04/31/2020. Not showing May month data. i want to display till May month.

[parry2k]

@harib did you read my previous reply if this doesn't work, provide sample data with details what is not working. I have no way to know what you are seeing and what is not working and how you are using the information. 

[harib]

@parry2k 

I have mentioned sample PBIX link. Please donwload and do the need ful.

Sample PBIX file : https://drive.google.com/file/d/1lwNNCQRV_nWMkfwdcrEeg89quWKsT98V/view?usp=sharing

Thanks

[parry2k]

@harib it is not available to download, need permission.

[harib]

@parry2k 

Oh sorry. 

Given permission. Now you can able to download the file .

Thanks