Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

calculation modification

 i have table  uptime and mint averger 

 

uptime  : 

Up time = CALCULATE(DIVIDE([calc op per day],[calc op per day manne]),FILTER('PA - E2PR',AND('PA - E2PR'[Year] = MAX(calender[Year]),'PA - E2PR'[Week Date]=MAX(DatePA[Week Date]))))
uptime mint average code 
Uptime MINT Average =
var a = SELECTEDVALUE(DatePA[monthno])
var yearend = ENDOFYEAR(calender[Date])
var result = IF(MAX('combine effective date'[month no])>=a && MAX('combine effective date'[Effective Date])<=yearend,[Increase in Average],BLANK())
return result
 
Increase in Average 
 
Increase in Average = var selectedvalue = DATE(YEAR(MAX(DatePA[Week Date])),MONTH(MAX(DatePA[Week Date])),DAY(2))
var a = DATE(YEAR(selectedvalue),MONTH(selectedvalue)+4,DAY(selectedvalue))
var b = DATE(YEAR(selectedvalue),MONTH(selectedvalue)+5,DAY(selectedvalue))
var c = DATE(YEAR(selectedvalue),MONTH(selectedvalue)+6,DAY(selectedvalue))
var d = DATE(YEAR(selectedvalue),MONTH(selectedvalue)+7,DAY(selectedvalue))



var selectedmonth = SELECTEDVALUE(DatePA[monthno])
var yearend = ENDOFYEAR(calender[Date])
var result = IF(MAX('combine effective date'[month no])>=selectedmonth && MAX('combine effective date'[Effective Date])<=a,[previous 3 Mint Average],
IF(MAX('combine effective date'[Effective Date]) > a && MAX('combine effective date'[Effective Date]) <= d,[previous 3 Mint Average]+0.01,
IF(MAX('combine effective date'[Effective Date]) > d ,[previous 3 Mint Average]+0.02)))

return result
 
previous 3 Mint Average = var selectedvalue = DATE(YEAR(MAX(DatePA[Week Date])),MONTH(MAX(DatePA[Week Date])),DAY(2))
var a = DATE(YEAR(selectedvalue),MONTH(selectedvalue)-1,DAY(selectedvalue))
var b = DATE(YEAR(selectedvalue),MONTH(selectedvalue)-2,DAY(selectedvalue))
var c = DATE(YEAR(selectedvalue),MONTH(selectedvalue)-3,DAY(selectedvalue))


var d = CALCULATE([Uptime]*0.5,'combine effective date'[Effective Date] >= a && 'combine effective date'[Effective Date] <= selectedvalue)
var e = CALCULATE([Uptime]*0.3,'combine effective date'[Effective Date] >= b && 'combine effective date'[Effective Date] <= a)
var f = CALCULATE([Uptime]*0.2,'combine effective date'[Effective Date] >= c && 'combine effective date'[Effective Date] <= b)
return d+e+f
 
April   64.2  * 0.5    = 32.1
 
May    65.3*0.3=     19.59
 
June      65.1*0.2=     13.71
 

previous month uptime avg  this value keep after selection months after three  it will increase +1 next months

 
 
 
 
i want if user select month previous month uptime caculate my  following 
if user select july previous three month april may june value mint value calulcate  avergae kept it same next three month aguest,sep,oct.
 
 
Help appreciated
Help me
Help Requested

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Not very clear. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    You can use measure like these with help from date table

     

    last 3 month allocated value

     

    CALCULATE([Uptime]*.5,DATESMTD(dateadd('Date'[Date],-1,MONTH)))+
    CALCULATE([Uptime]*.3,DATESMTD(dateadd('Date'[Date],-2,MONTH)))+
    CALCULATE([Uptime]*.2,DATESMTD(dateadd('Date'[Date],-3,MONTH)))


    Rolling 3 Avg = CALCULATE(averageX('Date'[Month Year],[Uptime]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak  

    IN chart there is two type value uptime previous month and uptime next . i am using line graph for yellow value

    suppose i choose week in slicer outbound25-06-2021 ,previous three be like below

    standdard calculation

    Yellow color value


    March 63.9 * 0.2 = 12.78

    April 65.1 * 0.3 = 19.53

    May 65.7 * 0.5 = 32.85

    avarge = 65.16

    Avaerge should common for first three month in blue colour july,August,September,

    after that next three month averge increase by +1 and last december increase +2

     

     

     

    Thanks in advance

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    amitchandak   i have shared files  . any update . it will really helpful for me if it work ..thank you  bro in advance