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

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

Accepted Solutions
Highlighted
Microsoft v-ljerr-msft
Microsoft

Re: Measure for Current Month

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
gregcarle Frequent Visitor
Frequent Visitor

Re: Measure for Current Month

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.

Microsoft dkay84_PowerBI
Microsoft

Re: Measure for Current Month

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

bhmiller89 Member
Member

Re: Measure for Current Month

Any suggestion on how to write this column? Do I first need to populate a date table?

Microsoft dkay84_PowerBI
Microsoft

Re: Measure for Current Month

Try this but adjust for month rather than week:

 

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

Highlighted
Microsoft v-ljerr-msft
Microsoft

Re: Measure for Current Month

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

bhmiller89 Member
Member

Re: Measure for Current Month

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

bhmiller89 Member
Member

Re: Measure for Current Month

@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? 

MansRydell Frequent Visitor
Frequent Visitor

Re: Measure for Current Month

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors