Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
9 years ago
Solved

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
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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

bhmiller89's avatar
bhmiller89
Helper V
9 years ago

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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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

  • bhmiller89's avatar
    bhmiller89
    Helper V
    9 years ago

    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's avatar
      bhmiller89
      Helper V
      9 years ago

      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? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.