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

10 Replies

  • gregcarle's avatar
    gregcarle
    Regular Visitor
    9 years ago

    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.

  • 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

  • nice_guy2131's avatar
    nice_guy2131
    New Member
    2 years ago

    Hi

    C_Monht = CALCULATE(COUNT('MB51 for 2023'[Material Document]),MONTH('MB51 for 2023'[Posting Date])=MONTH(NOW())
     
    To get the current month