Show Purchased/Returned Date using Calendar Table

[Anonymous]

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 .

[v-yetao1-msft · Accepted solution]

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.

[Anonymous]

Thank you, @v-yetao1-msft for sharing your dashboard to better explain the implementation using USERRELATIONSHIP(...). 

[amitchandak]

@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])

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

@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.