cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
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
Highlighted
Regular 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.

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

Highlighted
Helper V
Helper V

Re: Measure for Current Month

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

Highlighted
Microsoft
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
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

Highlighted
Helper V
Helper V

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) 

Highlighted
Helper V
Helper V

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? 

Highlighted
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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors