Open Date and Ship Date

[Omega]

Hi, 

I have common issue with dates in DAX but this time with a small twist. I have 2 tables, transaction and date. 

Transaction Table has two date columns and value column:

1- Order Date: The date I'm putting the order

2- Shipping Date: The data I'm shipping the order

3- Value

Date Table is full year Calendar

I am connecting both transaction date with calendar using order date (Active relationship) and with shipping date (inactive)

Challenge:

I want to calculate the total shipment for the first 10 order dates of month that will be shipped in full month of the order date + 2

For example, I have orders that I put in January which will be shipped in March. I want to create a measure that will calculate the total shipment by filtering order date to be from 1-10 and ship date to be full March. My month slicer will be based on the Date Table. If I choose Jan, the measure should return 100. 

Please help. 

Thanks 🙂

[nandukrishnavs · Accepted solution]

@Omega 

Try this 

Total =
VAR _month =
    SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
VAR _result =
    CALCULATE (
        SUM ( 'Transaction'[Value] ),
        FILTER (
            'Transaction',
            MONTH ( 'Transaction'[Order Date] ) = _month
                && DAY ( 'Transaction'[Order Date] ) < 11
                && MONTH ( 'Transaction'[Shipping Date] )
                    = MOD ( _month + 2, 12 )
        )
    )
RETURN
    _result

Output

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂