Connecting a fact table with multiple date columns to the date table

[qs]

Hello,

I have two tables. A fact table of "buying date", "Selling date" and fruit list. The second table is the date table. https://app.powerbi.com/groups/me/workbooks/163ca3f1-4bdd-4c1c-a43c-0ef27fa1e29c

I would like to display in a table the number of fruits bought and sold for a given day, week or month.

The formula for calculate and userlationship is much appreciated.

Best

QS

[v-jiascu-msft]

Hi @qs,

We can't open the link. You can upload the sample to the cloud drive like OneDrive, Dropbox. Then share the link here. Seems your needs are simple. You can drag the day, week, month from the date table, then use the built-in aggregation.

Best Regards,

Dale

[qs]

Dale,
It would be great to have DAX formula.

Best

Q

[v-jiascu-msft]

Hi @qs,

1. Create two valid date columns because the original date have time parts.

ValidBuyingDate = DATEVALUE([BuyingDate])

ValidSellingDate = DATEVALUE([SellingDate])

2. Create two relationships.

3. Create two measures.

bought = COUNT(Table1[Fruit])

Sold =
CALCULATE (
    COUNT ( Table1[Fruit] ),
    USERELATIONSHIP ( 'Calendar'[Date], Table1[ValidSellingDate] )
)

You can check out the demo in the attachment.

Best Regards,

Dale

[Anonymous]

Awesomeness ...  

[qs · Accepted solution]

Dale,

Thanks so much for the visual explanation and DAX formula. It's going to solve many issues for me.
Best

Qmars

[qs]

Hi  Dale,

Please find below a link to the excel file:
https://www.dropbox.com/s/3dlko7sgmw2vukf/Fruit%20Basket.xlsx?dl=0
Look forward to hearing from you.