topic Re: calculation modification in DAX Commands and Tips

calculation modification

Anonymous — Fri, 03 Sep 2021 06:16:29 GMT

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.

Re: calculation modification

amitchandak — Fri, 03 Sep 2021 05:41:40 GMT

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))

Re: calculation modification

Anonymous — Fri, 03 Sep 2021 10:27:41 GMT

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

Re: calculation modification

Anonymous — Fri, 03 Sep 2021 08:41:55 GMT

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