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
VuongLM93's avatar
VuongLM93
Helper III
4 years ago

Greenwoodr v-frfei-msft  I know this is old, but I have the same questions ...which approach to calculate the Sales for each "campaign" per customer

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 ?