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

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.

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

    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.