Forum Discussion
Create relarionship between a DATE and a RANGE OF DATES?
Hello,
I just started PowerBI for a few days,
currently I have some problem merging 2 tables in terms of date,
the data sample looks like below:
(1)Table A: Order fact which contains [OrderDate],[OrderNo]and other details
OrderDate OrderNo Qty ItemID ...
2016/03/03 001 1 123
2016/05/01 002 3 456
(2)Table B: A table of Sales campaign data which contains [Camp_ID][StartDate],[EndDate],[ItemID] and others
Camp_ID StartDate EndDate ItemID ...
CP001 2016/03/01 2016/04/01 123 ...
CP002 2016/03/15 2016/06/01 456 ...
What I'm trying to do is to create a relationship between these 2 tables that I can use [Camp_ID] as a filter for analysis.
I tried to create a unique key in each table to link them but it looks very unrealistic and non-expandable;
(e.x. CAMP_key = IF(DATEVALUE("06/01/2014")<=[OrderDate]&&[OrderDate]<DATEVALUE("07/31/2014") ,"CAMP1",
IF(DATEVALUE("08/01/2014")<=[OrderDate]&&[OrderDate]<DATEVALUE("08/31/2014") ,"CAMP2",
IF(DATEVALUE("10/01/2014")<=[OrderDate]&&[OrderDate]<DATEVALUE("10/31/2014")&&[ItemCD]="8287512" ,"CAMP3",
...........
Please let me know if you need any info as I think I didn't explain it very clearly.
ANY ideas and advice to help with this problem will be Greatly appeciated. Thank you very much!
I would add another fact-table that allocates the sales to each campaign-day. That's a bit like a budget-table holding planned sales for multiple planning events (multiple campaigns) for the same dates and items. This allows for relative simple measures then:
AvgCampSales = AVERAGE('CampaignSales'[CampaignSales])The table can be created like this:
CampaignSales = GENERATE(Camapaign, FILTER(ALLNOBLANKROW(DimDate[Date]), DimDate[Date]>=EARLIER(Camapaign[CampaignStartDate]) && DimDate[Date]<=EARLIER(Camapaign[CampaignEndDate])))
& you need an additional column to it:
CampaignSales = 'Order'[AvgSalesAmt]
You need to connect it like this:
Have a look a the file:
https://www.dropbox.com/s/sx28mk4c00ip740/PBI_CampaignManagement5.pbix?dl=0
9 Replies
Don't worry dude i will help u .
In both Table u have Date and Camp_ID right ? if yes do one thing
create calculated column on both table like
Column = Format( " Your Date " , "YYYYMMDD") & "-" & Camp_ID
this will work if u have unique column in Date and Camp_ID at anyone table end.
let me know wore help on DAX. I wil help u
Have a look if you find some useful information in here:
http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/
http://www.wiseowl.co.uk/blog/s2472/multiple-relationships.htm
As you will see, is that the right way to do this depends on what you actually want to analyze further. So for a recommendation we would need some details about your specific demand.
Hi!
Great thanks for the info first, yes I'll explain more detail about what I'm trying to do;
So you can see here as example, I have 2 tables 'DM_F_Order' and 'M_Campaign',
What I would like to do is the analysis of this:
Calculate the sum of Sales qty(or sum of sales amount) of each itemCD (Sales qty info is stored in 'DM_F_Orders'), which is to be filtered by Campaign_ID, average sales amount by day during campaign days, compared with yearly average of each Item.
**Edit: The relationship(physically) between the 2 tables is, when Order and Campaign table have common ItemCD and OrderDate is located between Camp_ST_DATE and Camp_ED_DATE, then the order row is considered as the (CP00X)campaign target. i.e. "IF (Order.ItemID=Campaign.ItemID AND Campaign.ST_DATE<=Order.OrderDate<=Campaign.ED_DATE), Campaign.Camp_ID"...
My idea was to build a link table between these 2 tables though the biggest struggle is I couldn't think of a key to build up relation for them.
Thanks for any advise!
Just want to add some more comment about this problem (as I haven't solve this out yet),
I tried to first use ALLSELECTED function to link Order table with Campaign table, set up a Campaign Key to build the relationship. The filter I thought in Order looks like this:
CAMPKey = IF([OrderDate]<=ALLSELECTED(M_Campaign[Camp_ED_DATE])&&ALLSELECTED(M_Campaign[Camp_ST_DATE])<=[OrderDate]&&[ItemCD]="1234",'M_Campaign'[Camp_CD],"NotCampaign")
(M_Campaign is the Campaign table which stores Campaign start date, end date)
Which I thought if I can describe "OrderDate is located between ST_DATE and _ED_DATE" WHERE Item Code = the Item Code in table Order,
though as imagined it didn:t work well, since I'm not sure how should I describe "WHERE" for the formula in my DAX.
Thanks again for ANY help!
Hi guys,
Thanks for all the comments! I have read them all and let me add some more info about my requirement if it helps.
ImkeF,about the question if there are multiple campaings for one Item ID, Yes there are.
The table is like this:
It can be multiple Campaign for one Item and it can be multiple items in the same campaign.
About the calender solution, yes I have created a calender to try to link the two tables.
The relationship now looks like this:
(Sorry if there are some JP characters just ignore them :)
The next problem is that there are 2 date column in M_Campaign (Start Date and End Date) which both are not Equal to OrderDate in Order table, so I am struggled here about how to build relation of them.
The issue I got here is there is actually no Camp_ID in Order table, which I think I may be able to handle it in SQL before taking it into PowerBI but stucked here when using DAX,
Though as you can see the screenshot of M_Campaign, those are the only infos contained in the table.
Another way to say my requirement is to join the Camp_ID into Order tables then I should be OK to do everything I need after then. :)
Again thanks for all the comments!
I would add another fact-table that allocates the sales to each campaign-day. That's a bit like a budget-table holding planned sales for multiple planning events (multiple campaigns) for the same dates and items. This allows for relative simple measures then:
AvgCampSales = AVERAGE('CampaignSales'[CampaignSales])The table can be created like this:
CampaignSales = GENERATE(Camapaign, FILTER(ALLNOBLANKROW(DimDate[Date]), DimDate[Date]>=EARLIER(Camapaign[CampaignStartDate]) && DimDate[Date]<=EARLIER(Camapaign[CampaignEndDate])))
& you need an additional column to it:
CampaignSales = 'Order'[AvgSalesAmt]
You need to connect it like this:
Have a look a the file:
https://www.dropbox.com/s/sx28mk4c00ip740/PBI_CampaignManagement5.pbix?dl=0
Hi,
Thanks for the clear sample!
I quicklt tried it with a little bit change (There are multiple camp_ID so couldn't do N:N relationship between Campaign and CampaignSales, I added a key column as [Date]&[ItemID] instead)
but currently the relationship seems works well and I think I'm able to play with the data. Thank you very much!
