Trend down analysis

[nagoor]

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). Could someone help me to get the similar results in Power bi and advise it is possible to perform here. 

The concept is to find the trend down on the last day compared with the last 30 days finding median and ABS, with the Threshold identified based on that..

https://www.dropbox.com/scl/fi/v2itvpt42hb8rjczwn413/Sample-File.xlsx?dl=0&rlkey=ec31pkfm8k10cfvmfrw...  

[v-angzheng-msft]

Hi, @nagoor 

Is the above answer helpful to you?
could you please mark the post which help as Answered? It means a lot to me, and it will help the others in the community find the solution easily if they face the same problem with you. Thank you.

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.

[nagoor]

Hi @v-angzheng-msft , 

Thanks for your support, it worked well.. 🙂 

[v-angzheng-msft · Accepted solution]

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.

[nagoor]

Thanks for your support @v-angzheng-msft  

However, if there are multiple Products, it doesnt work with this way 😞 

Also, is there any simple way if we have multiple KPIs too, like System availbility, System usage and other Key parameters are there ?

really appreciate your support. 

[v-angzheng-msft]

Hi, @nagoor 

An indicator requires at least one measure.
Please consdier sharing more details about it or a simple sample file without any sesentive information  so that i may work out with a workaround.

Best Regards,
Community Support Team _ Zeon Zheng

[nagoor]

HI @v-angzheng-msft , 

Thanks for your support.. 

Please find the attached sample file and help me to get it in power bi

Sample File 2 

[v-angzheng-msft]

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.