cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Getting value from start of this month

Hello everyone,

I want to retrieve a "HEADCOUNT" value on the first day of the current month, using DAX.

How should I approach this? I feel like the answer's right in front of me, but have not had luck so far.

Thanks.

1 ACCEPTED SOLUTION
Super User

Hi,

I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

``````Headcount measure: =
CALCULATE (
'TableName'[capturedate]
= EOMONTH ( TODAY (), -1 ) + 1
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

4 REPLIES 4
Super User

Something like this?

VAR CurrentMonth = MONTH ('Table'[capturedate])
VAR CurrentYear = YEAR ('Table'[capturedate])
VAR MinDateInMonth =
MINX(
FILTER (
ALL('Table'[capturedate]),
MONTH ('Table'[capturedate]) = CurrentMonth&&
YEAR ('Table'[capturedate]) = CurrentYear
),
'Table'[capturedate]
)
VAR ValueInMonth =
CALCULATE(
'Table'[capturedate] = MinDateInMonth,
ALL()
)
RETURN
ValueInMonth
Super User

Hi,

I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

``````Headcount measure: =
CALCULATE (
'TableName'[capturedate]
= EOMONTH ( TODAY (), -1 ) + 1
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Thank you, it worked!

Is there also a way to extract the same value from the first day, but of the previous month (instead of the current)?

Super User

Hi,

Please try writing a measure like below.

The link down below, that describes about EOMONTH DAX function, might help.

``````Headcount measure Prev.month: =
CALCULATE (
'TableName'[capturedate]
= EOMONTH ( TODAY (), -2 ) + 1
)``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.