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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

New measure to give a rate of SUM to MAX filtered by a value of third column for the latest month

Hello experts,

 

I'm trying to get a rate (%) that will be the sum of Hours / Max value of Estimated filtered by Code "SR" for the latest month.

 

Here is a sample of my data.

 

I have been stuck here for hours... Any help is much appreciated.

Thank you

 

Untitled.jpg

1 ACCEPTED SOLUTION
Cmcmahan
Resident Rockstar
Resident Rockstar

So for example, for July in your example data, this measure should return (.5+.75+.25)/9.96 = 15.06%?

 

Your measure would look like this:

SR Rate = CALCULATE( SUM(Table1[Hours]) / MAX(Table1[Estimated]), FILTER(Table1, Table1[Code] = "SR" && MONTH(Table1[Date]) = MONTH(NOW())-1)

View solution in original post

3 REPLIES 3
Cmcmahan
Resident Rockstar
Resident Rockstar

So for example, for July in your example data, this measure should return (.5+.75+.25)/9.96 = 15.06%?

 

Your measure would look like this:

SR Rate = CALCULATE( SUM(Table1[Hours]) / MAX(Table1[Estimated]), FILTER(Table1, Table1[Code] = "SR" && MONTH(Table1[Date]) = MONTH(NOW())-1)
Anonymous
Not applicable

Hi @Cmcmahan 

thank you vey much for your reply.

It did work indeed.

 

Could you please advice what I should change in the formula to get result for the previous month as well?

Thanks again!

July is the current previous month.  On September first, this would calculate for August data.

 

If you want to change what month is being calculated, change the month filter criteria. 

MONTH(Table1[Date]) = MONTH(NOW())-1

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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