Getting value from start of this month

[SoundChimera]

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.

[FreemanZ]

Something like this?

HEADCOUNT2 =

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(

     VALUES('Table'[headcount]),  

    'Table'[capturedate] = MinDateInMonth,

    ALL()

)

RETURN

ValueInMonth

[Jihwan_Kim · Accepted solution]

Hi,

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

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

[SoundChimera]

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

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Please try writing a measure like below.

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

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

 https://dax.guide/eomonth/