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

    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