Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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" )
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 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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@harib it is not available to download, need permission.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |