Using active and inactive in one measure

[bendejnp]

Hello,

Below is a simplified example of the dataset showing the orders with an order date and a date for requisted delivery. 

Example data:

order date and requested delivery date are connected with a calendar:

Now I need a measure that fullfils the following:

• Turnover (month to date is important) summed up
• with order date in current month (MTD)
• with requested delivery in current month (MTD)

Until now I managed how to create a measure with either order date MTD or requested delivery date MTD (with userrelationshop to use the inactive connection), but not both at the same time. 

Can you guys help me?

[v-cazheng-msft]

Hi @bendejnp ,

You may try this Measure.

Turnover MTD =
VAR Today_ =
    TODAY ()
VAR FirstDayOfMonth =
    DATE ( YEAR ( Today_ ), MONTH ( Today_ ), 1 )
RETURN
    CALCULATE (
        SUM ( 'Append'[Turnover] ),
        FILTER (
            'Append',
            'Append'[Order Date(YYYY/MM/DD)] >= FirstDayOfMonth
                && 'Append'[Requested Delivery Date(YYYY/MM/DD)] >= FirstDayOfMonth
        )
    )

Sample data:

The result looks like this.

Also, attached the pbix file as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[johnt75]

You could try

Turnover MTD = 
var mtd = DATESMTD( 'Calendar'[Date])
return CALCULATE( SUM('Table'[Turnover]),
TREATAS( mtd, 'Table'[Order date]),
TREATAS( mtd, 'Table'[Delivery date])
)

[bendejnp]

I tried it and it gives a figure, but it's much too low. Unfortunately I couldn't find out which part of data it slices. Any idea?

[johnt75]

Try the below in DAX Studio

DEFINE
    VAR mtd =
        DATESMTD ( 'Calendar'[Date] )

EVALUATE
CALCULATETABLE (
    'Table',
    TREATAS ( mtd, 'Table'[Order date] ),
    TREATAS ( mtd, 'Table'[Delivery date] )
)

That will show the rows which are being summed up. If you compare that to the rows which you think should be included you might be able to see any critical differences

[bendejnp]

Update: Ok I found out it works only for the selected day, but it does not sum up month to date. So this part seems not to work:

var mtd = DATESMTD( 'Calendar'[Date])

[johnt75]

Hmm, that could just be DAX Studio not having a context for the dates, where your report actually does.

Try

DEFINE
    VAR mtd =
        CALCULATETABLE (
            DATESMTD ( 'Calendar'[Date] ),
            TREATAS ( { TODAY () }, 'Calendar'[Date] )
        )

EVALUATE
CALCULATETABLE (
    'Table',
    TREATAS ( mtd, 'Table'[Order date] ),
    TREATAS ( mtd, 'Table'[Delivery date] )
)

[bendejnp]

Sorry my last post was not regarding DAX studio. I was typing whilst you already answered. I don't have DAX studio and cannot install on my business laptop.

However I found out the formula shows only the lines order date of the selected calendar day. Example:

The measure shows 435€, but it should shows 1690.

[johnt75 · Accepted solution]

OK, try

Turnover MTD = 
var mtd = DATESMTD( 'Calendar'[Date])
return CALCULATE( SUM('Table'[Turnover]),
REMOVEFILTERS('Calendar'),
TREATAS( mtd, 'Table'[Order date]),
TREATAS( mtd, 'Table'[Delivery date])
)

You may need to alter the REMOVEFILTERS if the order day & month aren't coming from the Calendar table.

[bendejnp]

With the suggested change it works now. Thank you! 

However, I didn't understand which the "removefilter" has and why I have to use it here 😄

[johnt75]

REMOVEFILTERS is to make sure that nothing is being filtered out by any slicers or filters applied to the calendar table.

[bendejnp]

Ok I see. So since the latest day is selected in the dashboard, it will only sum up the selected day. Thanks for the explanation.