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

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'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?

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

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