Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Get value for current month

Hi there,

I have a matrix table with numerous different values for different timescales, I wanted to know how to get a value for the current month? It's more complex than I thought. 

I have a simple measure called [User Count] which is what I need counting over the time period, then I have a date table built around [active_date] as shown below:

Dates = ADDCOLUMNS ( CALENDAR (FIRSTDATE('Candidate Activity'[active_date]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )
 
Do you know how I can build a simple measure that will calculate [User Count] for the current month of [active_date] ? 

4 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Please use the column from the date table "Dates" as the fields of table visual?

    And measure like the following expression:

    Measure =
CALCULATE (
    [User Count],
    FILTER (
        ALLSELECTED ( 'Candidate Activity' ),
        YEAR ( 'Candidate Activity'[active_date] ) = SELECTEDVALUE ( Dates[year] )
            && MONTH ( 'Candidate Activity'[active_date] )
                = SELECTEDVALUE ( Date[MonthNumber] )
    )
)

     

    Or you can share some example data and expect result if you need more help.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-chenwuz-msft 

      Thank you for the response, the measure had no errors itself, but when I put it into a visual it has an error, and this is the error:

      Error Message:
      MdxScript(Model) (40, 16) Calculation error in measure 'Candidate Activity'[In last Month]: DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

      I'm not sure why this is? I shouldn't convert the [active_date] should I? 

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi Anonymous ,

         

        Please change the type of the two columns in your table which you have mentioned in this measure.

        Or use the VALUE or FORMAT function to convert one of the values.

         

        If you need more help, please share your pbix file without sensitive data.

        Best Regards

        Community Support Team _ chenwu zhu