Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
KA95
Helper III
Helper III

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 4
v-chenwuz-msft
Community Support
Community Support

Hi @KA95 ,

 

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.

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? 

Hi @KA95 ,

 

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

Hi @v-chenwuz-msft 

I've changed the [MonthNumber] column in dates table to a whole number, but now the data is just blank, here is what it looks like when I put it on my visual below: 

KA95_0-1655367973457.png

This is what the date table looks like below:

KA95_1-1655368042221.png

I did notice that when I converted the MonthNumber into a wholenumber it went from 01 to 1, 02 to 2, etc. Could this be the problem maybe? Or does that not matter? 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors