Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
qs
Helper I
Helper I

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

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

1 ACCEPTED SOLUTION

Dale,

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

Qmars

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

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.

Connecting_a_fact_table_with_multiple_date_columns_to_the_date_table

 

Best Regards,

Dale

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

Dale,
It would be great to have DAX formula.

Best

Q

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.

Connecting_a_fact_table_with_multiple_date_columns_to_the_date_table2

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Awesomeness ...  

Dale,

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

Qmars

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.