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
jl20
Helper IV
Helper IV

Grossing Up Based on Year Dimension for Current Period

Hi,

 

I am trying to find a way to gross up a measure based on the year. For example, let's assume $100 of revenue in 2019, $150 in 2020, and $70 in 2021 (YTD through May). On the visual, I want to gross this up based on time elapsed to date, such that 2021 would equal $70 / (5/12) = $168. Is there a way to write an IF statement as a variable that says, if year = 2021, gross-up, otherwise multiply by 1?

 

Thanks in advance.

 

Desired Output

YearRevenue
2021168
2020150
2019100
1 ACCEPTED SOLUTION

Hello @jl20 

 

Gross Up Valuation Revenue Per FTE

Kumail_0-1624385246199.png

The updated file is also attached for your reference.

Kumail_1-1624385290086.png

https://drive.google.com/file/d/1qdWCfz4FYLN5rlUGwusIqpUi8bJk_OgA/view?usp=sharing

 

Hope this helps.

 

Regards

Kumail Raza

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

@jl20 as a best practice if someone provides a solution you have to confirm back whether it worked or not, just a bit of etiquette.

 

Now in this file which measure you are referring to? You have to be very precise in your requirement, just cannot think the other person will do the guessing game on behalf of you.

 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Sorry, parry2k. Will be sure to provide responses to each proposed solution going forward. In the file, the field that needs to be annualized is "Valuation Revenue per FTE by Fiscal Year" - specifically for FY21 as it's still ongoing.

parry2k
Super User
Super User

@jl20 I already gave you the measure, did you try it?

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Unfortunately, I couldn't get it to won't work given the structure of the data (see sample file). Revenue in my initial example is a measure built on two underlying measures.

Kumail
Post Prodigy
Post Prodigy

Hello @jl20  

 

If you could provide sample .pbix file, that would help providing a quick solution to your requirement.

I hope this helps. 

 

Regards

Kumail Raza

Here's a link to a sample file: https://drive.google.com/file/d/1c4AAhaKI23otzuajcX4xf9pVfQPjAt-x/view?usp=sharing 

 

Thanks for taking a look

Hello @jl20 

 

Gross Up Valuation Revenue Per FTE

Kumail_0-1624385246199.png

The updated file is also attached for your reference.

Kumail_1-1624385290086.png

https://drive.google.com/file/d/1qdWCfz4FYLN5rlUGwusIqpUi8bJk_OgA/view?usp=sharing

 

Hope this helps.

 

Regards

Kumail Raza

Kumail,

 

Awesome solution! This works perfectly. Thanks!

@jl20 You're welcome!

 

Regards

Kumail Raza

parry2k
Super User
Super User

@jl20 

 

New Revenue Measure = 
DIVIDE ( [Revenue], 
IF ( MAX ( Table[Year] ) = YEAR( TODAY(), DIVIDE ( MONTH ( TODAY() ) - 1, 12 ), 1 ) 
)

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

jl20
Helper IV
Helper IV

I actually simplified it in this example - revenue is a measure, so I somehow need to apply the adjustment to that rather than as a calc column.

parry2k
Super User
Super User

@jl20 sure you can do this, assuming revenue is a column

 

 

New Revenue Column = 
DIVIDE ( Table[Revenue], 
IF ( Table[Year] = YEAR( TODAY(), DIVIDE ( MONTH ( TODAY() ) - 1, 12 ), 1 ) 
)

 

 

Tweak the code as you see fit.

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.