I would like to have result 3 Rolling Rate (divide Defect parts / Shipping)

[redray82]

Hi

I would like to have result with 3month rolling rate for the defect per shipping qty.

Please refer to the attched excel file example. (and if you could give me a PBIX file it would  be grate..)

I just update same two excel file in case of error.

example excel file 1 

example excel file 2 

1. I have both 3mont rolling sum (refer to the yellow color)for defect parts and shipping product.. and i would like to have 3 month rolling rate (refer to the orange color) as below.

(ex 3month Rolling sum mean is If i choose Mar 19 = Jan 19 + Feb 19 + Mar 19)

2. Then i would like add line chart with 3 month rolling rate with Year, Year Quarter(ex. 2019/Q1), Year Month(ex 2019/01, 2019/02...)

Please refer to thea attached excel file and below raw data

Thank you

[v-yuta-msft]

@redray82 ,

Could you please also share the logic of "have 3 month rolling rate"?

Regards,

Jimmy Tao

[redray82]

Here is the Formula.

And i would like to have line chart with Yearly(Y2017, Y2018, Y2019,,..), Half Year (2019 1st H and 2019 2nd H), Quarter (2019/Q1,.2019/Q2..), Monthly (2019/01, 2019/02...) drill down chart...

if you could attache PBIx file, it would be good.

[redray82]

If you could add pbix file.. please help me...

Actually i made 3month rolling issue (no. 2 Blue box) and 3month rolling shipping (no. 4 Blue box)  Qty and Rate (no. 5 Blue box) ..

(please refer to the below picture for the blue box)

As you can see, the monthly rate are correctly calculated...
But Quarterly and half yearly, year rate are not correctly calculated...(refer to the Blue Line...)

i attached the rate result and formula ..

Please help me how to make line chart with correct rate for yearly, Half yearly (ex. 2019 1st Half, 2nd Half..), Quarterly (2019/Q1, 2019/Q2....), Monthly (2019/01, 2019/02...)

[v-yuta-msft]

@redray82 ,

Create three measures using dax as below:

3 Months Rolling Defect Qty = 
VAR SelectedMonth = EOMONTH(SELECTEDVALUE(Defect[Fiscal year]), 0)
VAR PreviousThreeMonth = EOMONTH(SELECTEDVALUE(Defect[Fiscal year]), -3) + 1
RETURN
CALCULATE(SUM(Defect[QTY]), FILTER(ALL(Defect), Defect[Fiscal year] >= PreviousThreeMonth && Defect[Fiscal year] <= SelectedMonth && Defect[Proudcts group] = SELECTEDVALUE(Defect[Proudcts group]) && Defect[Code group] = SELECTEDVALUE(Defect[Code group])))

3 Months Rolling Shipping Qty = 
VAR SelectedMonth = EOMONTH(SELECTEDVALUE(Shipping[Fiscal year]), 0)
VAR PreviousThreeMonth = EOMONTH(SELECTEDVALUE(Shipping[Fiscal year]), -3) + 1
RETURN
CALCULATE(SUM(Shipping[QTY]), FILTER(ALL(Shipping), Shipping[Fiscal year] >= PreviousThreeMonth && Shipping[Fiscal year] <= SelectedMonth && Shipping[Proudcts group] = SELECTEDVALUE(Shipping[Proudcts group])))

3 Month Rolling Rate = [3 Months Rolling Defect Qty] / [3 Months Rolling Shipping Qty]

The result will be like below:

 

You can also refer to the pbix file.

Community Support Team _ Jimmy Tao

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

[redray82]

Thank you for your rely..

is it possible to make the Table.. with Actual Issue Qty, 3M Rolling Issue Qty, Actual Shipping Qty, 3M Shipping Qty and Rate

with Yearly, Half Yearly (2019 1st Half.. 2nd Half), Quartrly (2019/Q1, ...), Monthly (2019/01, 2019/03 ....)?

And also could you please draw and checked Line chart? because when I draw the line chart... data were incorrectly pull out...

[v-yuta-msft · Accepted solution]

@redray82 ,

I'm afraid this is impossible because power bi doesn't support dynamic calculate table/column.

Community Support Team _ Jimmy Tao

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