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
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!
ImkeF is right - you cannot create a relationship with a DAX expression. You must create a dimension table in your data model that you can then use to create relationships with the two "fact" or "data" tables you mention above.
It seems that the best solution in your case would be to create a "Date" or "Calendar" table. If you plan to add new order dates and campaign dates to your data, you'll want it to be a dynamic Date table that continues to incorporate new dates as time moves forward.
This post describes in detail how you would create one using "Power Query" in Excel. Fortunately, Power Query is hiding inside Power BI, so you can follow the exact same steps in Power BI, but you'll want to know the following:
1. Instead of starting from the "Other Sources" button in Excel, you'll use the "Get Data" button in Power BI. From the dropdown, you'll see the same "Blank Query" option he references.
2. Instead of clicking the "Invoke" button, simply hit "enter."
3. Find the earliest date that you care about in your data and enter it as the start date. Follow his steps to do the rest.
4. Once you've created all the columns you want in your Date table, rename the query in the "Query Settings" pane on the right to "Calendar" or "Dates."
5. Click Home>Close & Apply
5. Then go to the "Relationships" view on the top left of your screen (third choice down), and you'll see your new table along with the others you've already loaded to your data model.
6. Click and drag from the "Date" field in your "Calendar" table to the fields in your "data" tables that contain dates. Make sure the data types of the date columns you're creating the relationship with are also set to the "Date" data type. This change can be made by clicking the "Edit Queries" button on the Home menu, choosing the query that creates your data table, selecting the column with dates, and choosing "Date" from the "Data Type" dropdown in the "Transform" group.
7. Once these relationships are created (from the "Calendar[Date]" column to one data table, and from the "Calendar[Date] column to the other data table), you can then go to the "Report" view (top left of your screen, top choice), right click on one of your data tables, and choose "New Measure" to create measures that allow you to return what you are wanting to return using data from both tables.
I hope this helps. Let me know if you need more detail or if you run into issues.