Data modeling load date and delivery date

[koushikB]

Community, I have a data as above for any date that is selected in between load date and delivery date I need to get the respective volume. For example, if the date filter is selected as 02/04/2017 then the result will be 2000. I need to build a model out of the table above. 

To do this, I had worked and found a solution but it has some limitations with it. What I did is create two fact tables with 

Fact 1: Date, Region, Transport, Load date, Volume

Fact 2: Date, Region, Transport, delivery date, Volume

and 

date dimension: date, week number and so on.

Then relationships are given as fact1.date to date dimention.date and fact2.date to date dimention.date

In fact 1: created a running total Laod: Calculate( sum('fact1'[Volume]),Filter(All('Fact1'), 'fact1'[date]<=max('fact1'[Date'])))

In fact 2: Created a running total Delivery: Calculate( sum('fact2'[Volume]),Filter(All('Fact2'), 'fact2'[date]<=max('fact2'[Date'])))

Now 

Third measure, with Total required volume: Running total delivery- Running total load

This way I was able to get the required volume for the specific date but If I  filter the 'total required volume' with region and Transport or any of it I was not able to get the result right. Is there a better way to make the design right. 

Thanks for your support. 

[v-jiascu-msft · Accepted solution]

Hi @koushikB,

Maybe you could try this formula.

There are two tables. Your data table called "Table1" and a date table "Calendar". They don't have any relationship. The result "ValidSum" will ignore the filters from date but still can be filtered by "Region" or "Transport".

ValidSum =
CALCULATE (
    SUMX (
        Table1,
        IF (
            HASONEVALUE ( 'Calendar'[Date] ),
            IF (
                'Table1'[Load Date] <= MIN ( 'Calendar'[Date] )
                    && Table1[Delivery Date] >= MIN ( 'Calendar'[Date] ),
                'Table1'[Volume],
                BLANK ()
            ),
            BLANK ()
        )
    ),
    ALL ( Table1[Load Date] ),
    ALL ( Table1[Delivery Date] )
)

Best Regards!

Dale

[koushikB]

@v-jiascu-msft Thanks

It is great way to solve. I am glad. 

[prateekraina]

Hi @koushikB,

Check out this video, it might help.

Prateek Raina