Forum Discussion

bendejnp's avatar
bendejnp
Icon for Helper I rankHelper I
4 years ago
Solved

Using active and inactive in one measure

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 ar...
  • johnt75's avatar
    johnt75
    4 years ago

    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.

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

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's avatar
bendejnp
Icon for Helper I rankHelper I
4 years ago

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's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      bendejnp
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

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