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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
James-L
New Member

Showing YTD costs as a proportion of average assets

Hello,

 

I would like to dynamically show the fee rate of YTD expenses as a function of total average assets a firm manages.

 

For example. If total costs in June was $500,000, and average assets for the year were $100,000,000 then the annualised fee rate would be:

 

$500,000 / 6 * 12 = $1,000,000 (Annualised Costs)

/

$100,000,000 (average assets for the year)

= 1%, 100bps

 

The formula will change when a different month is selected but I am not sure how to do this dynamically. I have attached a draft PBI file and an example in excel of what I am looking to do.

 

Thank you in advance for your help.

 

JamesL_0-1709070656924.pngJamesL_1-1709070693817.png

 

My date table looks like this:

 

JamesL_2-1709070754357.png

 

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@James-L , Make sure you are using a date table, date of date table is joined with date of you table and use field from the date table in visual, measure and slicer

 

Try a meausre like

 

Divide( CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")), month(Max(Date[Date])) )*12

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@James-L , Make sure you are using a date table, date of date table is joined with date of you table and use field from the date table in visual, measure and slicer

 

Try a meausre like

 

Divide( CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31")), month(Max(Date[Date])) )*12

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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