First and Last Value - Custom Measure

[pwrbipv]

Hi,

How is the first and last value determined by Power Bi?

I created a Power Bi desktop report using a custom measure of my design.  The measure returns a user-entered value in the Task  Defined field in the data warehouse.  Power Bi desktop uses a SQL Server Analysis Services database. The "*1" is because the user-input is returned as a text field so multitplying it by converts it to a number.

Computed Funds Allocated YTD = MAX(('Hierarchy'[Task Defined]))*1

There are six sub-tasks in one job.  I am using a slicer with single selection radio buttons to allow the report user to select the specific sub-task to be viewed.  The report works great with the exception of one sub-task.  In fact, every visulaization works perfectly for all six sub-tasks, returning the correct info, with the exception of one visulaization for one sub-task.  Again, five of the six sub-tasks return the correct amount for one visualization using the above custom measure.  However, one of the six only returns the correct measure when the custom measure uses "MIN" instead of "MAX" 

Using "MAX" returns the sum of the four entries into the data warehouse for five sub-tasks but only returns the sum of the first two entries for the sixth sub-task; it is not returning the sum of all four entries.  Any thoughts as to why? Is there a quick work-around, short of adding a new visualization? I don"t want a two visualizations where one visulaization will have the correct info for one sub-task but incorrect info for five subtasks, and vice-versa.

Thank you.

[pwrbipv]

Ok, thanks for the help.  I am unable to provide access to you to my employer's database so therefore I am unable to provide sample data.  I can do the following so will this help?:

job #1

sub task #1:  new funds in Nov so the entry in the data warehouse is updated from zero to $100,000   

new funds of $40,000 in Dec so the entry in the data warehouse is updated to $140,000 

new funds of $110,000 in Jan so the entry in the data warehouse is updated to $250,000 

new funds of $15,000 in Feb so the entry in the data warehouse is updated to $265,000  

using the custom measure:    Computed Funds Allocated YTD = MAX(('Hierarchy'[Task Defined]))    returns $265,000       this is the correct response - it is the most recent updated entry

sub task #2:  new funds in Nov so the entry in the data warehouse is updated from zero to $80,000

new funds in Decs of $47,000 the entry in the data warehouse is updated to $127,000

new funds in Feb of $103,000 so the entry in the data warehouse is updated to $230,000

new funds in Apr of $75,000 so the entry in the data warehouse is updated to $305,000   

usinf the exact same custom measure:    Computed Funds Allocated YTD = MAX(('Hierarchy'[Task Defined]))  returns $127,000........why is this total incorrect and only the sum of the first two entries, $127,000?  It should be the total of all four entries, like it is for sub task #1, and should return $305,000.   But, if I change the custom measure to use "MIN", insted of "MAX", like this....   Computed Funds Allocated YTD = MIN(('Hierarchy'[Task Defined]))        returns $305,000

I am trying to figure out why for just ONE of SIX sub tasks I get the incorrect response using MAX.  If I change the custom measure to MIN to get it to work for the one subtask then the other five subtasks  return incorrect amounts.  So, this led me to ask how are the last & first values determined.  I was starting to think it was when the entires were entered but.....there have been two entries and two updates after the first two for subtask #2.  I just cannot figure out why.

[lbendlin]

The "*1" is because the user-input is returned as a text field so multitplying it by converts it to a number.

Red flag.  Note that "10"  is not larger than "8". MAX will give you the largest text, alphabetically sorted.

[pwrbipv]

Hi, Thanks for the reply but I am not understanding.  In my question I do nto mention a 10.  I shoudl have just left the "*1" off the equation as it seems to have confused the issue.

I am trying to determine why one visualization returns the correct info, which is the sum of four individual entries made over the past 7 months in the funding field, for five subtasks using "MAX" but for one subtask using "MAX" only returns the sum of the first two entries made in the funding field over the past seven mnths.  For this one subtask using "MIN" returns the correct info but then the five subtasks have the wrong info.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...