Return the cumulative value for the max date of a range selected by user

[DavidWaters100]

Hi,

I am trying to return the cumulative value for the max date of a range selected by user per below:

The end column is a movement for a single day and the middle column is cumualtive of all previous days to that point.  So first row, cumulative was 454, then a movment on 15/01/19 of -271 resulted in new cumulative of 183 (454-271).

What I need to do is get a measure to return the cumulative amount for the highest day a user selects in a between date filter:

Example - user selects dates between 15/01/19 and 30/04/19 means cumulative position is 289 (value for max date in this range).

I have tried a number of ways but cannot get it to work!  Any help greatly appreciated! 

[DavidWaters100]

Hi @v-rzhou-msft 

I have implemented this solution but I get the "expression refers to multiple columns....cannot be converted to scalar value".  Any ideas to resolve please - am looking into it

[v-rzhou-msft]

Hi @DavidWaters100 

If the result of your measure or calculated column returns to a table(multiple columns instead of single values), you will see this dax error.

You may use calculate function like: 

calculate(table[column1],filter(table,table[column2]= value))

Here is a blog for this dax error, and I hope it could help you solve your problem.

Blog : DAX Error: The Expression Refers to Multiple Columns. Multiple Columns Cannot Be Converted to a Scal...

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[DavidWaters100]

Hi @v-rzhou-msft 

Many thanks for your response.  I have looked at the example file you added and I can see that the solution is doing what I require.  I think I just need to join the calendar table to my main table but that should be fine to do.  Many thanks again, this is really appreciated!

[v-rzhou-msft · Accepted solution]

Hi @DavidWaters100 

I build a sample to have a test.

My sample table:

You can build a calendar table and use date column in it to build a slicer.

Date = CALENDAR(MIN('Table'[Movement Date]),MAX('Table'[Movement Date]))

My measures:

Cumulative Value in Max Date = 
Var _MAXDate = MAX('Date'[Date])
Return
SUMX(FILTER('Table','Table'[Movement Date]<=_MAXDate),'Table'[Movement])

Cumulative Value in Max Date 2 = 
Var _MAXDate = MAX('Date'[Date])
Var _MAXMovementDate = MAXX(FILTER('Table','Table'[Movement Date]<=_MAXDate),'Table'[Movement Date])
Return
SUMX(FILTER('Table','Table'[Movement Date]=_MAXMovementDate),'Table'[Cumulative value])

You can calculate cumulative value for the highest day by Movement column directly by measure1 or you can calculate the higest day in Table which date<= higest date in slicer, and then calculate the cumulative value by the date.

Result is as below.

You can download the pbix file from this link: Return the cumulative value for the max date of a range selected by user

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.