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?

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

        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.