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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SoundChimera
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.

 

SoundChimera_0-1668999274227.png

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
Jihwan_Kim
Super User
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 (
    MAX ( 'TableName'[HEADCOUNT] ),
    '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.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
FreemanZ
Super User
Super User

Something like this?

 

FreemanZ_0-1669010195388.png

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
Super User
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 (
    MAX ( 'TableName'[HEADCOUNT] ),
    '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.


Go to My LinkedIn Page


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

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/

 

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.