Hi,
I come to you because i'm trying to find a way to solve the problem as following :
here is the context : i ve got 4 tables.
Relationships :
Collab[Tri] 1____* Prevision[Name]
Cal[Date] 1----* Prevision[Date]
Cal[Date] 1____* Collab[Date]
Collab[Tri] 1____* Command[Name]
Cal[Date] 1____* Command[Start Date]
As you can see, there's no direct relation between Command table and Prevision table.
Datas
Collab = ["Tri","Date","Agency","Company","Seller"]
Cal... Well, it's a calendar table
Prevision = ["Name","Date (month)","WorkDays nb"]
Command = ["Start Date", "End Date", "Ref","RefPost","Name", "ClientName","PriceDay","Quantity"]
*Ref is a global command (like "Cars")
RefPost is an unitary command (like "Tesla P85D") under a global command.
Request
For each Agency (Collab table), client (Command table), seller (Collab table), Collab(names in Collab table), Ref (Command table), RefPost (Command table), start date and end date (Command table), i would like to have : quantity (number of days bought in Command table), PriceDay (Command table), and workdays nb (Prevision table) for each year/quarter/month
here s an sample :
| | | | | | | | | | 2021 | | |
| | | | | | | | | | Q3 | | |
| Company | Agency | Client | Seller | Collab | Ref | RefPost | Start | End | Quantity | PriceDay | Wordays nb |
| Company1 | Agency1 | Client1 | Seller1 | Collab1 | Ref1 | RefPost1.1 | 07/01/21 | 09/30/21 | 60 | 500 | 50 |
| | | | Seller2 | Collab2 | Ref2 | RefPost2.1 | 07/01/21 | 09/30/21 | 60 | 500 | 55 |
| | | | | Collab3 | Ref2 | RefPost2.2 | 08/01/21 | 09/30/21 | 45 | 640 | 45 |
| | Agency2 | Client1 | Seller3 | Collab4 | Ref2 | RefPost2.3 | 09/01/21 | 12/31/21 | 19 | 530 | 18 |
etc...
I tried with some DAX measures, but can't make it... must be tired!!!
Thank you for helping me
