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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ngomez
Regular Visitor

Calculated Measure with variable start date

Hello guys!

 

I need help on calculating a financial return value. I'm using a calculated measure, and I need it to be able to use a slicer to define different starting points for the calculation.

 

The first value is 100.000, and then it multiplies for each day return. I want to change the date and the value to adjust to that starting point. My measure is defined as:

 

VALUE = CALCULATE(100000*PRODUCT(Returns[R100C]), FILTER(ALLEXCEPT(Returns, Returns[R100C],Calendar[Date]), Returns[date] <= MAX(Returns[date])))
 
I have a Calendar Table to use in the slicer, but it currently doen't take the slicer filter to do the calculation.
 
It looks like this for the whole dates period, which is ok:
 
img1.PNG
 
But when changing dates on the slicer, it only shows that period without calculating for the new starting date:img2.PNG 
 
Any thoughts on how could I fix this?
 
Thanks!
4 REPLIES 4
v-kelly-msft
Community Support
Community Support

Hi @ngomez

 

Try to modify your measure as below:

 

 

VALUE = CALCULATE(100000*PRODUCT(Returns[R100C]), FILTER(ALLEXCEPT(Returns, Returns[R100C],Returns[date]), Returns[date] in Filters 'Calendar'[date])))

 

 

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
amitchandak
Super User
Super User

@ngomez ,Can you share sample data and sample output.

@ngomez 

If you want to show the beginning of the chart at 100,000 for all lines, you would have to divide by the value(s) at the first date of the period you are showing, which i guess is MIN(Returns[date])

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

SU18_powerbi_badge

AlB
Super User
Super User

Hi @ngomez 

What would the expected result look like?

Can you show more of your data model? Or ideally the pbix?

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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