Cumulative Total with USERELATIONSHIP

DAX DAX Calculations
1

Hello all!

I have one table with the close price of some stocks. In other table I have the transactions done (buy and sell) so with that I have the quantity.

I’m trying to visualize the accumulated quantity per stock and multiply that for the close price but I’m not being able to do it.

I can get the quantity and take it to the quote table using USERELATIONSHIP but when I try to accumulate this measure is not working, I’m using this:

Accumulated Stock Qty =

CALCULATE(
‘#Measures’[UR Stock Qty],
FILTER(
ALL(
‘Calendar’),
‘Calendar’[Fecha] <= MAX(‘Calendar’[Fecha]
)))
Hope anyone can help me with this.
I’m copying a link to the PBI file below:

*** READ FIRST ***

Before you send your question.
Make sure that all details relevant to your question is complete:

  • Your current work-in-progress PBIX file - VERY IMPORTANT
  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your underlying data file

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Also make sure that your data file contains no confidential information. If it does, click the link above.

*** DELETE THIS MESSAGE IF YOU ARE SURE ALL YOUR DETAILS ARE COMPLETE OR IF THE ABOVE INFORMATION IS NOT APPLICABLE TO YOUR QUESTION.***

2

Hello @aoh. Thanks for reaching out to the Forum. Try using ALLSELECTED instead of All for Calendar [Dates]. Then you want to refer to Calendar again to be <= MAX [Dates].

ALL will just look at the highest date value since the beginning of your date table and ALLSELECTED will consider all the dates in the context of your report.

Measure =

CALCULATE(
[UR Stock Qty],
FILTER(
ALLSELECTED ( ‘Date’[Date] ),
‘Date’[Date] <= MAX(‘Date’[Date]) ) )

More information is provided in the link below for ALL and ALLSELECTED. I hope this helps.

Paul

3

Hello @Paul.Gerber! Thank you for your answer.

I’m using ALLSELECTED and now it appears to be accumulating the values but a problem occurs. Now the column in the table “Close price” it’s showing values that doesn’t correspond to the specific ticker.
I’m not sure if I need to correct the measure or the data model.

I’m adding a picture below, hope it helps make it clear.

image

4

@aoh I would look closely at your relationships. You have a bidirectional one. Keep a table as a fact table like Transactions. Then use TREATAS to get info from your Quotes table. I will work on this also.

You are missing dates in your dates table.

Paul

5

Hello @aoh

Did the response from @Paul.Gerber help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark his answer as the SOLUTION.

Thank you

6

Hi @aoh ,

Due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Thanks to @Paul.Gerber for contributing to this post.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!