Hi there,
Kindly help to have DAX formula for the below table
each item has different amount either issued or received (+1 or -1) and i need to have average cost of an each item irrespective of date
[TABLE="width: 0"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Item No.[/TD]
[TD]Item Qty[/TD]
[TD]Description[/TD]
[TD] Amount [/TD]
[/TR]
[TR]
[TD]31/03/2019 [/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 6,076,135.41 [/TD]
[/TR]
[TR]
[TD]31/01/2019[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 5,532,795.11 [/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 4,959,654.48 [/TD]
[/TR]
[TR]
[TD]31/10/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 4,909,921.49 [/TD]
[/TR]
[TR]
[TD]30/11/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 4,900,504.26 [/TD]
[/TR]
[TR]
[TD]30/09/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 4,253,778.16 [/TD]
[/TR]
[TR]
[TD]28/02/2019[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 3,767,478.03 [/TD]
[/TR]
[TR]
[TD]30/06/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 2,925,793.64 [/TD]
[/TR]
[TR]
[TD]24/07/2018[/TD]
[TD]161W2100-2[/TD]
[TD]-1[/TD]
[TD]MOMPONENT ADDY - MLG[/TD]
[TD]- 2,425,042.25 [/TD]
[/TR]
[TR]
[TD]01/07/2018[/TD]
[TD]161W2100-2[/TD]
[TD]1[/TD]
[TD]MOMPONENT ADDY - MLG[/TD]
[TD] 2,210,740.01 [/TD]
[/TR]
[TR]
[TD]24/07/2018[/TD]
[TD]162W1100-14[/TD]
[TD]-1[/TD]
[TD]DHOMKDTKUTADDY[/TD]
[TD]- 2,210,726.43 [/TD]
[/TR]
[TR]
[TD]26/02/2019[/TD]
[TD]B445000-038[/TD]
[TD]-1[/TD]
[TD]TOP MOLLEMTOK[/TD]
[TD]- 1,964,821.76 [/TD]
[/TR]
[TR]
[TD]31/05/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 1,753,442.25 [/TD]
[/TR]
[TR]
[TD]31/10/2018[/TD]
[TD]201585003-020[/TD]
[TD]-1[/TD]
[TD]LEG AND DKEDDINGD-MLG[/TD]
[TD]- 1,605,675.73 [/TD]
[/TR]
[TR]
[TD]30/04/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 1,452,260.74 [/TD]
[/TR]
[TR]
[TD]21/05/2018[/TD]
[TD]201585003-020[/TD]
[TD]1[/TD]
[TD]LEG AND DKEDDINGD-MLG[/TD]
[TD] 1,279,996.79 [/TD]
[/TR]
[TR]
[TD]31/05/2018[/TD]
[TD]201585003-020[/TD]
[TD]-1[/TD]
[TD]LEG AND DKEDDINGD-MLG[/TD]
[TD]- 1,278,030.38 [/TD]
[/TR]
[TR]
[TD]01/03/2019[/TD]
[TD]47172-500[/TD]
[TD]1[/TD]
[TD]AMTUATOK - THD[/TD]
[TD] 1,274,525.00 [/TD]
[/TR]
[TR]
[TD]30/09/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 1,110,838.92 [/TD]
[/TR]
[TR]
[TD]23/10/2018[/TD]
[TD]683L250G03[/TD]
[TD]1[/TD]
[TD]KIT -FAN KEVEKDEK PODN 2 KH [/TD]
[TD] 1,108,387.97 [/TD]
[/TR]
[TR]
[TD]16/07/2018[/TD]
[TD]F71AR010000200[/TD]
[TD]1[/TD]
[TD]AIK INLET NODE MOWL[/TD]
[TD] 1,087,393.38 [/TD]
[/TR]
[TR]
[TD]26/10/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 866,205.24 [/TD]
[/TR]
[TR]
[TD]20/10/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 861,315.77 [/TD]
[/TR]
[TR]
[TD]31/08/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 858,814.37 [/TD]
[/TR]
[TR]
[TD]03/06/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 833,621.72 [/TD]
[/TR]
[TR]
[TD]08/08/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 809,483.84 [/TD]
[/TR]
[TR]
[TD]07/11/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 803,936.94 [/TD]
[/TR]
[TR]
[TD]01/12/2018[/TD]
[TD]161W2130-1[/TD]
[TD]1[/TD]
[TD]TKUMK MOMPONENT ADDEMBLY[/TD]
[TD] 792,355.77 [/TD]
[/TR]
[TR]
[TD]01/12/2018[/TD]
[TD]161W4200-10[/TD]
[TD]1[/TD]
[TD]DTKUT ADDY - DKAG[/TD]
[TD] 792,355.77 [/TD]
[/TR]
[TR]
[TD]01/02/2019[/TD]
[TD]161W2100-1[/TD]
[TD]1[/TD]
[TD]MOMPONENT ADDY - MLG[/TD]
[TD] 792,355.77 [/TD]
[/TR]
[TR]
[TD]01/02/2019[/TD]
[TD]161W2100-2[/TD]
[TD]1[/TD]
[TD]MOMPONENT ADDY - MLG[/TD]
[TD] 792,355.77 [/TD]
[/TR]
[TR]
[TD]30/04/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 787,462.24
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks
Kindly help to have DAX formula for the below table
each item has different amount either issued or received (+1 or -1) and i need to have average cost of an each item irrespective of date
[TABLE="width: 0"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Item No.[/TD]
[TD]Item Qty[/TD]
[TD]Description[/TD]
[TD] Amount [/TD]
[/TR]
[TR]
[TD]31/03/2019 [/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 6,076,135.41 [/TD]
[/TR]
[TR]
[TD]31/01/2019[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 5,532,795.11 [/TD]
[/TR]
[TR]
[TD]31/12/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 4,959,654.48 [/TD]
[/TR]
[TR]
[TD]31/10/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 4,909,921.49 [/TD]
[/TR]
[TR]
[TD]30/11/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 4,900,504.26 [/TD]
[/TR]
[TR]
[TD]30/09/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 4,253,778.16 [/TD]
[/TR]
[TR]
[TD]28/02/2019[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 3,767,478.03 [/TD]
[/TR]
[TR]
[TD]30/06/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 2,925,793.64 [/TD]
[/TR]
[TR]
[TD]24/07/2018[/TD]
[TD]161W2100-2[/TD]
[TD]-1[/TD]
[TD]MOMPONENT ADDY - MLG[/TD]
[TD]- 2,425,042.25 [/TD]
[/TR]
[TR]
[TD]01/07/2018[/TD]
[TD]161W2100-2[/TD]
[TD]1[/TD]
[TD]MOMPONENT ADDY - MLG[/TD]
[TD] 2,210,740.01 [/TD]
[/TR]
[TR]
[TD]24/07/2018[/TD]
[TD]162W1100-14[/TD]
[TD]-1[/TD]
[TD]DHOMKDTKUTADDY[/TD]
[TD]- 2,210,726.43 [/TD]
[/TR]
[TR]
[TD]26/02/2019[/TD]
[TD]B445000-038[/TD]
[TD]-1[/TD]
[TD]TOP MOLLEMTOK[/TD]
[TD]- 1,964,821.76 [/TD]
[/TR]
[TR]
[TD]31/05/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 1,753,442.25 [/TD]
[/TR]
[TR]
[TD]31/10/2018[/TD]
[TD]201585003-020[/TD]
[TD]-1[/TD]
[TD]LEG AND DKEDDINGD-MLG[/TD]
[TD]- 1,605,675.73 [/TD]
[/TR]
[TR]
[TD]30/04/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 1,452,260.74 [/TD]
[/TR]
[TR]
[TD]21/05/2018[/TD]
[TD]201585003-020[/TD]
[TD]1[/TD]
[TD]LEG AND DKEDDINGD-MLG[/TD]
[TD] 1,279,996.79 [/TD]
[/TR]
[TR]
[TD]31/05/2018[/TD]
[TD]201585003-020[/TD]
[TD]-1[/TD]
[TD]LEG AND DKEDDINGD-MLG[/TD]
[TD]- 1,278,030.38 [/TD]
[/TR]
[TR]
[TD]01/03/2019[/TD]
[TD]47172-500[/TD]
[TD]1[/TD]
[TD]AMTUATOK - THD[/TD]
[TD] 1,274,525.00 [/TD]
[/TR]
[TR]
[TD]30/09/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 1,110,838.92 [/TD]
[/TR]
[TR]
[TD]23/10/2018[/TD]
[TD]683L250G03[/TD]
[TD]1[/TD]
[TD]KIT -FAN KEVEKDEK PODN 2 KH [/TD]
[TD] 1,108,387.97 [/TD]
[/TR]
[TR]
[TD]16/07/2018[/TD]
[TD]F71AR010000200[/TD]
[TD]1[/TD]
[TD]AIK INLET NODE MOWL[/TD]
[TD] 1,087,393.38 [/TD]
[/TR]
[TR]
[TD]26/10/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 866,205.24 [/TD]
[/TR]
[TR]
[TD]20/10/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 861,315.77 [/TD]
[/TR]
[TR]
[TD]31/08/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 858,814.37 [/TD]
[/TR]
[TR]
[TD]03/06/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 833,621.72 [/TD]
[/TR]
[TR]
[TD]08/08/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 809,483.84 [/TD]
[/TR]
[TR]
[TD]07/11/2018[/TD]
[TD]7010101H09[/TD]
[TD]1[/TD]
[TD]MAM - MABIN AIK MOMPKEDDOK[/TD]
[TD] 803,936.94 [/TD]
[/TR]
[TR]
[TD]01/12/2018[/TD]
[TD]161W2130-1[/TD]
[TD]1[/TD]
[TD]TKUMK MOMPONENT ADDEMBLY[/TD]
[TD] 792,355.77 [/TD]
[/TR]
[TR]
[TD]01/12/2018[/TD]
[TD]161W4200-10[/TD]
[TD]1[/TD]
[TD]DTKUT ADDY - DKAG[/TD]
[TD] 792,355.77 [/TD]
[/TR]
[TR]
[TD]01/02/2019[/TD]
[TD]161W2100-1[/TD]
[TD]1[/TD]
[TD]MOMPONENT ADDY - MLG[/TD]
[TD] 792,355.77 [/TD]
[/TR]
[TR]
[TD]01/02/2019[/TD]
[TD]161W2100-2[/TD]
[TD]1[/TD]
[TD]MOMPONENT ADDY - MLG[/TD]
[TD] 792,355.77 [/TD]
[/TR]
[TR]
[TD]30/04/2018[/TD]
[TD]In house[/TD]
[TD]1[/TD]
[TD]In houDe[/TD]
[TD] 787,462.24
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks