cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bhmiller89
Helper V
Helper V

Measure for Current Month

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

1 ACCEPTED 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.

t1.PNG

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"
    )

c1.PNGr1.PNG

Regards

View solution in original post

8 REPLIES 8
dkay84_PowerBI
Microsoft
Microsoft

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.

t1.PNG

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"
    )

c1.PNGr1.PNG

Regards

View solution in original post

@v-ljerr-msft

 

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) 

@v-ljerr-msft

 


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? 

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.

 

YearMonthCur  =
IF (
YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )
&& MONTH ( 'Date'[Date] ) = MONTH ( TODAY () );
"Current Month";
FORMAT ( 'Date'[YearMonth]; "" )
)
 
I had a preexisting YearMonth column that I used.

Try this but adjust for month rather than week:

 

https://blog.oraylis.de/2016/02/relative-week-column-with-dax-power-query/

gregcarle
Regular Visitor

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors