Forum Discussion

chienim's avatar
chienim
Frequent Visitor
9 years ago
Solved

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 [Or...
  • ImkeF's avatar
    ImkeF
    9 years ago

    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

chienim's avatar
chienim
Frequent Visitor
9 years ago

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.

 

bourquejeff,

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.

 

Baskar,

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!

ImkeF's avatar
ImkeF
Community Champion
9 years ago

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

  • chienim's avatar
    chienim
    Frequent Visitor
    9 years ago

    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!