cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vickyd Member
Member

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
AlexisOlson Member
Member

Re: Calculate measure for latest selected month

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

Community Support Team
Community Support Team

Re: Calculate measure for latest selected month

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

Community Support Team
Community Support Team

Re: Calculate measure for latest selected month

Hi vickyd,

 

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

 

Regards,

Jimmy Tao

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 278 members 2,743 guests
Please welcome our newest community members: