Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Tony Wambui]

Hello Folks,
I seem to be running into a bind trying to put together what I think should be a simple formula to give the values for the latest (Max date) in my data.  I have created a dynamic formula to give me the latest date in my data using “Latest Date = MAX(Combined_Ridership1[Date])“. As you can see in the snippet  below, I am then using this newly created measure as an input to a longer formula to give the value (in this case ridership) for the Max day in my data so that I can create a dynamic card that would display the latest ridership numbers. However, the formula is giving me the total ridership numbers (111,170) for all the dates in my data (matrix on the left) instead of just 361 which is the ridership for 06/16/2020 (max date with ridership numbers in my data). How can I modify my formula to give me a dynamic out put of ridership values for my Max Date such that if update the database with 06/17/2020 values (let’s say 370), the card should correspondingly update to show the new max date ridership values?

Thank you in advance for your help and time.

Best, 
Tony

——————————
Tony Wambui
Program Analyst
——————————

[Lutz Bendlin]

when you say “dynamic formula” what do you mean – did you create a measure or did you create a calculated column?

Depending on your particular scenario one or the other would be the right approach.  Calculated columns are calculated ONCE during each data refresh.  Measures, in contrast, are calculated EVERY TIME your users interact with your report.

——————————
Lutz
——————————
——————————————-

[Tony Wambui]

I created a measure

——————————————-

[Lutz Bendlin]

in that case you need to break out of the filter context

Latest Date = calculate(MAX(Combined_Ridership1[Date]),ALL(Combined_Ridership1))

——————————
Lutz
——————————
——————————————-

[Tony Wambui]

Thank you so much Lutz, your formula resolution worked very well! And just to make sure that I fully understand your resolution well, which filter context are you breaking out of using the ALL function? This is the part that I am not fully comprehending.

Again, many thanks Lutz!

Best!

——————————
Tony Wambui
Program Analyst
——————————
——————————————-