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 are connected with a calendar:

 

 

Now I need a measure that fullfils the following:

  • Turnover (month to date is important) summed up
  • with order date in current month (MTD)
  • with requested delivery in current month (MTD)

 

Until now I managed how to create a measure with either order date MTD or requested delivery date MTD (with userrelationshop to use the inactive connection), but not both at the same time. 

 

Can you guys help me?

  • 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.

11 Replies

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

         

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi bendejnp ,

     

    You may try this Measure.

    Turnover MTD =
VAR Today_ =
    TODAY ()
VAR FirstDayOfMonth =
    DATE ( YEAR ( Today_ ), MONTH ( Today_ ), 1 )
RETURN
    CALCULATE (
        SUM ( 'Append'[Turnover] ),
        FILTER (
            'Append',
            'Append'[Order Date(YYYY/MM/DD)] >= FirstDayOfMonth
                && 'Append'[Requested Delivery Date(YYYY/MM/DD)] >= FirstDayOfMonth
        )
    )

     

    Sample data:

     

    The result looks like this.

     

    Also, attached the pbix file as reference.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun