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.

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

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

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