Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a list of Opportunities and they all have create dates and close dates (projected and actual) I want to show only Opportunities created in the current month
Solved! Go to Solution.
Hi @bhmiller89,
In this scenario, you don't need to populate a date table. And I have created a simple sample about how to create the calculate column which indicates if the "create Date" is within current month for your reference.
I assume you have a table called "Table1" like below.
Then you should be able to use the formula below to create the calculate column, and use the created column as Slicer to show only Opportunities created in the current month.
IsCurrentMonth = IF ( YEAR ( Table1[Create Date] ) = YEAR ( TODAY () ) && MONTH ( Table1[Create Date] ) = MONTH ( TODAY () ), "Yes", "No" )
Regards
Hi
You need to make a custom column that determines if the create date is in the current month and returns a "True" or "False". Then you could filter by "True" to display only those opportunities created in the current month.
Similarly, if you want to create a measure that calculates something based on those opportunites, you would use a filter argument to look at those that are "True".
Any suggestion on how to write this column? Do I first need to populate a date table?
Hi @bhmiller89,
In this scenario, you don't need to populate a date table. And I have created a simple sample about how to create the calculate column which indicates if the "create Date" is within current month for your reference.
I assume you have a table called "Table1" like below.
Then you should be able to use the formula below to create the calculate column, and use the created column as Slicer to show only Opportunities created in the current month.
IsCurrentMonth = IF ( YEAR ( Table1[Create Date] ) = YEAR ( TODAY () ) && MONTH ( Table1[Create Date] ) = MONTH ( TODAY () ), "Yes", "No" )
Regards
how would I write a column to show IsNextMonth and also IsMonthAfterNext?
this column worked great but I also need to show next month and the month after (3 months total)
Tried
IsNextMonthClose = IF('dpmgr vwJMWebSalesOpportunitiesProduction'[CloseDate]= YEAR(TODAY()) && MONTH('dpmgr vwJMWebSalesOpportunitiesProduction'[CloseDate]= MONTH(TODAY()+1)), "Yes", "No")
but it's not working. is the "+1" not correct?
Hello,
Just fell on this today, I know the post is old, as I was looking to do something similar and I tried this solution and I think it isn't working because you are adding 1 day to today rather than the month. I tried adding 29 days to today or having MONTH(TODAY())+1) and it seems to have worked.
Hi
Maybe this is a solution.
I used this to make a yearmonth slicer with current month set as default. If you combine it with dynamic date filter for next 2 month it might do what you are after. Unsure how it will handel the days before current day in current month though.
Try this but adjust for month rather than week:
https://blog.oraylis.de/2016/02/relative-week-column-with-dax-power-query/
You can create a new field (which would be a yes/no flag) within you Data that calculates days from Today(). If within x number of days, then yes.
Then use the column as a filter.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |