Forum Discussion
Create relarionship between a DATE and a RANGE OF DATES?
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
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!