Forum Discussion

nagoor's avatar
nagoor
Helper III
4 years ago
Solved

Trend down analysis

Dear Experts,    Hope everyone is good..!!    I came across one requirement to find the trend down analysis compared with the last 30 days data, i have manage to derive it in excel (attached file...
Need Help
Tips and Tricks
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, nagoor 

     

    I recreated my measures. I tried to make the measures as reusable as possible.
    Here are my measures:

    30daysTable:

    _30daysTable = 
var _30day=[_maxDate]-1
var _t=ADDCOLUMNS('Table',"maxdate",[_maxDate])
var _t1=
    FILTER(
        FILTER(_t,'Table'[Product Name]="AA1"),
        'Table'[Date]<=_30day&&'Table'[Date]>=_30day-30)
//==========================================================
var _t2=
    FILTER(
        FILTER(_t,'Table'[Product Name]="AA2"),
        'Table'[Date]<=_30day&&'Table'[Date]>=_30day-30)
//==========================================================
// if there are more product, _t3..._tn
var _tt=UNION(_t1,_t2)
// union(t1,t2....tn)
return _tt

    Median, ABS, The value of lastest date, Thredshold, Result:

    The above 5 measures are reusable parts, only need to change keywords to become the value of another indicator.
    I will not expand it here because it contains a long code, please see the attachment for details.

    The part that needs to be modified I include it with an equal line.

    eg:

    Result:

    The reusable part, I've considered whether to implement it with a calculation group, but unfortunately the tabular editor doesn't work on my machine. Maybe you can go ahead and implement it.

     

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
4 years ago

Hi, nagoor 

 

1. create a measure to get the lastest date:

_The date = CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))

2. create a calculate table(30days):

30days = 
var _30date=[_The date]-1
var _t=FILTER(ALLSELECTED('Table'),'Table'[Date]<=_30date&&'Table'[Date]>=_30date-30)
return _t

3. Median:

_Median = CALCULATE(MEDIAN('30days'[System Availability (%)]),ALL('30days'))

4. ABS:

_(ABS) = ABS(MIN('30days'[System Availability (%)])-[_Median])
_ABS = 
var _t=SELECTCOLUMNS('30days',"Date",[Date],"Product Name",[Product Name],"abs",[_(ABS)])
var _t29=FILTER(_t,[Date]<[_The date]-1)
return MEDIANX(_t29,[abs])

5. Thredshold:

Defination = -1
_Threshold = [_Median]+[Defination]*[_ABS]*3.7

6. Result:

Results = 
var _maxDate_value=CALCULATE(MAX('Table'[System Availability (%)]),FILTER(ALL('Table'),'Table'[Date]=[_The date]))
var _if=IF([Defination]=-1,IF(_maxDate_value<[_Threshold],1,0),IF(_maxDate_value>[_Threshold],1,0))
return _if

 

So we get the result like this:

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.