Forum Discussion

orangeatom's avatar
orangeatom
Resolver I
5 years ago
Solved

Measure Trend Description

I have a simple report page, a slicer that has the relative date and a matrix that has customers, quantity by month. The dataset is:

customer,qty,date

A,2,2020-08-01

A,0,2020-09-01

A,-7,2020-10-01

A,12,2020-11-01

A,22,2020-12-01

 

I would like to get a measure calculates the trend description so using the example above the measure trend description would say 'positive trend'. Other options include i would like to include are 'negative trend', 'stable trend'.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi orangeatom ,

     

    Check the measure.

    Measure = 
var last_date = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[date]<SELECTEDVALUE('Table'[date])))
var last_qty = CALCULATE(SUM('Table'[qty]),FILTER(ALL('Table'),'Table'[date]=last_date))
var current_qty = SELECTEDVALUE('Table'[qty])
return
SWITCH(TRUE(),ISBLANK(last_qty)||current_qty=last_qty,"stable trend",current_qty<last_qty,"negative trend",current_qty>last_qty,"positive trend")

    Result would be shown as below.

     

    Best Regards,

    Jay

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello orangeatom ,
    Do you want something like this?
    If yes, please try the below calculations for the calculated column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi orangeatom ,

     

    Check the measure.

    Measure = 
var last_date = CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[date]<SELECTEDVALUE('Table'[date])))
var last_qty = CALCULATE(SUM('Table'[qty]),FILTER(ALL('Table'),'Table'[date]=last_date))
var current_qty = SELECTEDVALUE('Table'[qty])
return
SWITCH(TRUE(),ISBLANK(last_qty)||current_qty=last_qty,"stable trend",current_qty<last_qty,"negative trend",current_qty>last_qty,"positive trend")

    Result would be shown as below.

     

    Best Regards,

    Jay