Forum Discussion

Greenwoodr's avatar
Greenwoodr
Helper I
7 years ago

Understanding Multiple Fact Tables

Hi I would be grateful for some assitance with the following data model design query.......     I have the following structure    1) Fact Table 1 Sales :(1 row per sale) : Contains details of s...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
4 years ago

Here is one way. First the sample model:

 With a SUM measure for sales, create the following:

 

Sales By Campaign & Customer =
VAR StartDate =
    CALCULATE (
        MIN ( 'Campaign Table'[Campaign Date] ),
        ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[Campaign] )
    )
VAR EndD =
    CALCULATE (
        MAX ( 'Campaign Table'[Campaign Date] ),
        ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[Campaign] )
    )
RETURN
    CALCULATE (
        [Sum Sales],
        CROSSFILTER ( 'Campaign Table'[Customer], 'Customer Table'[Customer Name], BOTH ),
        FILTER (
            ALL ( 'Sales Table'[Sales Date] ),
            'Sales Table'[Sales Date] >= StartDate
                && 'Sales Table'[Sales Date] <= EndD
        )
    )

 

 to get:

 

I've attached the sample PBIX file

VuongLM93's avatar
VuongLM93
Helper III
4 years ago

Thanks for the approach, my data for each customer, there is also specific "start date" and "end date" columns   ( for example a 50% discount program apply for customer A , from 1/1/2021 to 20/1/2021 ) , how to calculate the sales for the campaign ?

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    It would be helpful if you provided sample data in tabular form

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        See if this works, though the performance is slow:

        Sales By Campaign & Customer =
VAR StartDate =
    CALCULATE (
        MAX ( 'Campaign Table'[Start Date] ),
        ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[CampaignID] )
    )
VAR StartKey =
    INT ( DAY ( StartDate ) & MONTH ( StartDate ) & YEAR ( StartDate ) )
VAR EndDate =
    CALCULATE (
        MAX ( 'Campaign Table'[End Date] ),
        ALLEXCEPT ( 'Campaign Table', 'Campaign Table'[CampaignID] )
    )
VAR EndKey =
    INT ( DAY ( EndDate ) & MONTH ( EndDate ) & YEAR ( EndDate ) )
RETURN
    CALCULATE (
        [Sum Sales],
        CROSSFILTER ( 'Campaign Table'[Customer Code], 'Customer Table'[Customer Code], BOTH ),
        FILTER (
            ALL ( 'Sales Table'[DateKey] ),
            'Sales Table'[DateKey] >= StartKey
                && 'Sales Table'[DateKey] <= EndKey
        )
    )

         

        I've attached the sample PBIX file