Forum Discussion
Show Purchased/Returned Date using Calendar Table
Hi, (pbix example below)
I have a table which show items with a purhcase and returned date as well as other info. I would like to create a visual which shows a date axis and considers both when the item was purchased and when it was returned.
Currently, I have a Calendar table which makes up the Date axis in the bar graph visual. However, it only considers the item at the time of purchase via relationship modeling. The date 3/15/2019 is not found for the one returned item. Is there a way to consider both dates in one graph? Thanks!
Thanks for any help! Check out the pbix file for this issue here .
Hi Anonymous
You cannot filter the two fields of the data table directly through the calendar date table, because there can only be one active relationship between the two tables. If you want to filter two dates at the same time, you need to create an active relationship and an inactive relationship between the two tables, and then use USERELATIONSHIP() to activate the inactive relationship. One thing to note, when you add the measure that creates the activation relationship to the corresponding visual, it will take effect
(1)Create two relationships between data table and calendar date table .
(2)Then create a measure to activate the inactive relationship.
Quantity by returned date = CALCULATE(SUM(Items[Quantity]),USERELATIONSHIP(Items[Date Returned],'Calendar'[Date]))(3) If you put two dates in the same chart, it may seem a bit messy, you can separate the purchase date and the return date into two charts.
I have attached my pbix file, you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
Anonymous , think you need to create an inactive relation with other date and use userelationship to active that
example
calculate( SUM(Table[Qunatity]),USERELATIONSHIP ('Table'[date returned], 'Date'[Date])
amitchandak Thank you for the resource! This really helped me a lot. I had no idea about the functionality behind inactive relations and USERRELATIONSHIP function.
Hi Anonymous
You cannot filter the two fields of the data table directly through the calendar date table, because there can only be one active relationship between the two tables. If you want to filter two dates at the same time, you need to create an active relationship and an inactive relationship between the two tables, and then use USERELATIONSHIP() to activate the inactive relationship. One thing to note, when you add the measure that creates the activation relationship to the corresponding visual, it will take effect
(1)Create two relationships between data table and calendar date table .
(2)Then create a measure to activate the inactive relationship.
Quantity by returned date = CALCULATE(SUM(Items[Quantity]),USERELATIONSHIP(Items[Date Returned],'Calendar'[Date]))(3) If you put two dates in the same chart, it may seem a bit messy, you can separate the purchase date and the return date into two charts.
I have attached my pbix file, you can refer to it .
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
