Forum Discussion

Omega's avatar
Omega
Icon for Impactful Individual rankImpactful Individual
6 years ago
Solved

Open Date and Ship Date

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 πŸ™‚

 

  • 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
    πŸ™‚

1 Reply

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion

    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
    πŸ™‚