Please help

[Zaky]

Hi Experts,

Here I have a challenges:

Appreciated if anyone can help me solve the puzzle.

1st challenge is where I want to get the running total the Remaining row.
The Remaining running total should be resulted when the total from the Carry Forward is deducted with the total Accumulative Return.

For example, if I filter Batch return date start from Week 2 of July 2020, the total of the Remaining
is starting 9263 and its getting lesser until the end.

Expected result like the table below:

2nd challenge is where I want to get a weekly performance % for each week.
The performance % should be resulted when the total from Accumulative Return is devide with the total
of Carry forward.

For example, if I filter Batch return date starting from Week 2 of July 2020, the percentage is 4%, week 3 = 2%, week 4 = 1% and so on.

Expected result like the table below:

Over the above, the carry forward total is results from the total of Overall (10315) and deducted
with the Previous Returned total (713). 10315-713 = 9602.

Btw, the working pbix file is link here  https://1drv.ms/u/s!AqKwe2kf8OBId3mrOrzFnMGPLJE?e=PCD2wa

Thanks

[v-jingzhang · Accepted solution]

Hi @Zaky 

Is this what you want? Please download the .pbix file to check more details.

I created some new measures starting with #M_ in the model. To display the measures on rows, you could go to Format > Values and turn on Show on rows.

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@Zaky , Try like

Remaining row = CALCULATE(COUNTROWS(Data2020),'Data2020'[Status] = "Returned",FILTER(ALLSELECTED(Data2020), 'Data2020'[Batch Return]>=MAX('Data2020'[Batch Return]))) -[#MeasureReturned2020]

[Zaky]

Dear @amitchandak 

Thanks for your help. But it is not the result that i want, as the measure has already been created 

and the measure that I used is

#COUNT SPECIFIC MEASURE CARRY FORWARD =

var a =

CALCULATE(

COUNTROWS('Data2020'), Data2020[Status] IN {"Collected From User","Intransit","Not Process Yet","Not Return By User","Not Yet Return","Pending Collection From User","Redeployed"},

IF(

YEAR('Data2020'[Batch Return])=2019 || YEAR('Data2020'[Batch Return])=BLANK(),1,0)

)

var b=

CALCULATE(

COUNTROWS('Data2020'),

FILTER(

Data2020,

((Data2020[Batch Return].[MonthNo])=3||(Data2020[Batch Return].[MonthNo])=4||(Data2020[Batch Return].[MonthNo])=5||(Data2020[Batch Return].[MonthNo])=6||(Data2020[Batch Return].[MonthNo])=7||(Data2020[Batch Return].[MonthNo])=8||(Data2020[Batch Return].[MonthNo])=9||(Data2020[Batch Return].[MonthNo])=10||(Data2020[Batch Return].[MonthNo])=11||(Data2020[Batch Return].[MonthNo])=12)&&YEAR(Data2020[Batch Return])=2020)

)

return

a+b

Thanks.

[v-jingzhang · Accepted solution]

Hi @Zaky 

Is this what you want? Please download the .pbix file to check more details.

I created some new measures starting with #M_ in the model. To display the measures on rows, you could go to Format > Values and turn on Show on rows.

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[Zaky · Accepted solution]

@v-jingzhang ,

You are the Master! Exactly as per what i want

Kudos 👍

I really appreciated of what you have done, and your effort to made this challenges a successfull.

Regards.

I love it!

[v-jingzhang]

@Zaky Thank you, glad it was helping 😃

Regards,

Jing