Forum Discussion

Helper III

4 years ago

Solved

TREATAS

HI All,

Need to find previousmonth sales using TREATAS

below is the sample code......

Sales in previous month =

VAR result1 =

CALCULATE(SUM(Order[sales]),

TREATAS (VALUES ('delivery-date-table'[Date]),

'Order-datetable'[date] )

)

var result2 =

CALCULATE(a,

PREVIOUSMONTH( 'Order-datetable'[date] )

return

result2

the problem is I can move the filter from delivery date table to order date table ( which has relationship with sales table)

but when I try to find the previousmonth data ( for delivery date table ), the treatas function can't help me here.

How to use TREATAS and PREVIOUSMONTH in calculated function ?

v-easonf-msft
4 years ago
Hi, vijay273162

TREATAS is used to apply the result of a table expression as filters to columns from an unrelated table.

You should make sure that here should be no physical relationship between Table "Orders" and Table "Delivery Dates Table".

Best Regards,
Community Support Team _ Eason

3 Replies

amitchandak
Super User
4 years ago
vijay273162 , You should use date table, Try like

CALCULATE(SUM(Order[sales]),
TREATAS (PREVIOUSMONTH( 'Order-datetable'[date] ),
'Order-datetable'[date] )
)

or

CALCULATE(SUM(Order[sales]),
TREATAS (PREVIOUSMONTH( 'Date'[date] ),
'Order-datetable'[date] )
)
- vijay273162
  Helper III
  4 years ago
  If I use PREVIOUSMONTH in TREATAS, then..... I don't get any result in my table matrxi below.
  
  So, I need to move the filter from delivery date table to order date table - at the sametime find the previousmonth data.
  - v-easonf-msft
    Community Support
    4 years ago
    Hi, vijay273162
    
    TREATAS is used to apply the result of a table expression as filters to columns from an unrelated table.
    
    You should make sure that here should be no physical relationship between Table "Orders" and Table "Delivery Dates Table".
    
    Best Regards,
    Community Support Team _ Eason

Related content