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

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago

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