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
vickyd
Helper V
Helper V

Calculate measure for latest selected month

I want to calculate a measure for the latest selected month e.g. if user selects a range between Jan to Nov, measure will be calculated for Nov. If user selects May to Jun, measure will be calculated for Jun.

 

Only other condition is that future weeks are always excluded. 

 

I've gotten it using the below code but wondering if there's an easier and more optimized way of achieving the same. 

 

 

MEASURE Metrics[All FTE (Latest Month)]=
VAR LatestMonthRollingNo = CALCULATE(MAX(WorkDateCalendar[Rolling Month No]), WorkDateCalendar[Rolling Week No] < 1)

RETURN
CALCULATE([All FTE], WorkDateCalendar[Rolling Month No] = LatestMonthRollingNo)

 

 

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

Hi vickyd,

 

Does the answer make sense? If it is, could you help mark the answer to finish the thread?

 

Regards,

Jimmy Tao

v-yuta-msft
Community Support
Community Support

Hi vickyd,

 

So your requirement is to achieve the lastest selected month and calculate the value based on the lastest month, right? You may try measure below and check if it can meet your requirement.

 

Metrics[All FTE (Latest Month)] =
VAR LatestMonthRollingNo =
    CALCULATE (
        MAX ( WorkDateCalendar[Rolling Month No] ),
        ALLSELECTED ( WorkDateCalendar[Rolling Week No] )
    )
RETURN
    CALCULATE (
        [All FTE],
        WorkDateCalendar[Rolling Month No] = LatestMonthRollingNo
    )

Regards,

Jimmy Tao

AlexisOlson
Super User
Super User

Depending on how your model is set up, you may be able to use the DATESMTD function.

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.