topic Re: Using active and inactive in one measure in DAX Commands and Tips

Using active and inactive in one measure

bendejnp — Wed, 13 Jul 2022 14:52:00 GMT

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?

Re: Using active and inactive in one measure

johnt75 — Wed, 13 Jul 2022 14:55:47 GMT

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]) )

Re: Using active and inactive in one measure

bendejnp — Wed, 13 Jul 2022 15:18:33 GMT

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?

Re: Using active and inactive in one measure

bendejnp — Wed, 13 Jul 2022 15:25:12 GMT

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])

Re: Using active and inactive in one measure

johnt75 — Wed, 13 Jul 2022 15:25:24 GMT

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

Re: Using active and inactive in one measure

johnt75 — Wed, 13 Jul 2022 15:31:17 GMT

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] ) )

Re: Using active and inactive in one measure

bendejnp — Wed, 13 Jul 2022 15:40:43 GMT

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.

Re: Using active and inactive in one measure

johnt75 — Wed, 13 Jul 2022 15:55:27 GMT

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.

Re: Using active and inactive in one measure

v-cazheng-msft — Mon, 18 Jul 2022 11:29:52 GMT

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

Re: Using active and inactive in one measure

bendejnp — Tue, 19 Jul 2022 07:25:35 GMT

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 😄

Re: Using active and inactive in one measure

johnt75 — Tue, 19 Jul 2022 08:32:16 GMT

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

Re: Using active and inactive in one measure

bendejnp — Tue, 19 Jul 2022 09:03:35 GMT

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.