Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

Reply
NawilatiMahmoud
Frequent Visitor

How to find the increment values of based on a historical data dates.

‎04-05-2023 05:48 AM

Hello Dears , 

 

I need help in finding the increment values of the "rate card amount" for the below table based on the employee full name and  the  rate card dates and filter by Regular Item Only:

 

Employee NumberEmployee Full NameRate Card DateYearCard Item NameCard Item AmountIncrement Amount
700Laith 9/15/1997 0:001997Regular430 
700Laith 2/27/2000 0:002000Regular430 
700Laith 1/1/2005 0:002005Regular510 
700Laith 1/1/2006 0:002006Regular670 
700Laith 2/1/2006 0:002006Regular800 
700Laith 9/1/2006 0:002006Regular1300 
700Laith 1/1/2007 0:002007Regular1350 
700Laith 1/1/2008 0:002008Regular1500 
700Laith 3/1/2008 0:002008Regular1800 
700Laith 1/1/2009 0:002009Regular2000 
700Laith 1/1/2010 0:002010Regular2150 
700Laith 2/1/2010 0:002010Regular2450 
700Laith 1/1/2011 0:002011Regular2630 
700Laith 1/1/2012 0:002012Regular2762 
700Laith 1/1/2013 0:002013Regular2900 
700Laith 4/1/2013 0:002013Regular3300 
700Laith 1/1/2014 0:002014Regular3465 
700Laith 1/1/2015 0:002015Regular3751 
700Laith 1/1/2016 0:002016Regular3939 
700Laith 3/31/2019 0:002019Bonus1973 
700Laith 4/1/2019 0:002019Regular4097 
700Laith 1/1/2020 0:002020Bonus8194 
700Laith 2/28/2021 0:002021Bonus6146 
700Laith 3/8/2022 0:002022Bonus10243 
700Laith 3/1/2023 0:002023Bonus11951 
800Ibrahim 3/31/2019 0:002019Bonus2450 
800Ibrahim 4/17/2001 0:002001Regular370 
800Ibrahim 1/1/2005 0:002005Regular420 
800Ibrahim 3/13/2005 0:002005Regular520 
800Ibrahim 1/1/2006 0:002006Regular570 
800Ibrahim 2/1/2006 0:002006Regular630 
800Ibrahim 10/1/2006 0:002006Regular705 
800Ibrahim 11/1/2006 0:002006Regular730 
800Ibrahim 1/1/2007 0:002007Regular800 
800Ibrahim 1/1/2008 0:002008Regular890 
800Ibrahim 1/1/2009 0:002009Regular1000 
800Ibrahim 7/1/2009 0:002009Regular1160 
800Ibrahim 1/1/2010 0:002010Regular1247 
800Ibrahim 1/1/2011 0:002011Regular1334 
800Ibrahim 1/1/2012 0:002012Regular1401 
800Ibrahim 1/1/2013 0:002013Regular1471 
800Ibrahim 1/1/2014 0:002014Regular1545 
800Ibrahim 1/1/2015 0:002015Regular1622 
800Ibrahim 3/1/2015 0:002015Regular1872 
800Ibrahim 1/1/2016 0:002016Regular1966 
800Ibrahim 3/1/2017 0:002017Regular2025 
800Ibrahim 5/1/2017 0:002017Regular2450 
800Ibrahim 4/1/2019 0:002019Regular2548 
800Ibrahim 1/1/2020 0:002020Bonus3822 
800Ibrahim 2/28/2021 0:002021Bonus3822 
800Ibrahim 3/8/2022 0:002022Bonus6370 
800Ibrahim 3/1/2023 0:002023Bonus6370 
900Yousef 3/31/2019 0:002019Bonus4650 
900Yousef 1/3/2004 0:002004Regular300 
900Yousef 1/1/2005 0:002005Regular350 
900Yousef 3/13/2005 0:002005Regular450 
900Yousef 1/1/2006 0:002006Regular500 
900Yousef 10/1/2006 0:002006Regular575 
900Yousef 11/1/2006 0:002006Regular600 
900Yousef 1/1/2007 0:002007Regular660 
900Yousef 8/1/2007 0:002007Regular760 
900Yousef 1/1/2008 0:002008Regular840 
900Yousef 1/1/2009 0:002009Regular1100 
900Yousef 2/1/2009 0:002009Regular1600 
900Yousef 1/1/2010 0:002010Regular1720 
900Yousef 1/1/2011 0:002011Regular1840 
900Yousef 1/1/2012 0:002012Regular1932 
900Yousef 1/1/2013 0:002013Regular2029 
900Yousef 1/1/2014 0:002014Regular2130 
900Yousef 1/1/2015 0:002015Regular2259 
900Yousef 1/1/2016 0:002016Regular2372 
900Yousef 3/1/2017 0:002017Regular2443 
900Yousef 5/1/2017 0:002017Regular3100 
900Yousef 4/1/2019 0:002019Regular3333 
900Yousef 1/1/2020 0:002020Bonus6666 
900Yousef 2/28/2021 0:002021Bonus9599 
900Yousef 3/8/2022 0:002022Bonus9999 
900Yousef 3/1/2023 0:002023Bonus9999 
670Nancy 1/11/2004 0:002004Regular300 
670Nancy 1/1/2005 0:002005Regular350 
670Nancy 1/1/2006 0:002006Regular425 
670Nancy 2/1/2006 0:002006Regular475 
670Nancy 1/1/2007 0:002007Regular550 
670Nancy 3/1/2007 0:002007Regular625 
670Nancy 1/1/2008 0:002008Regular700 
670Nancy 3/1/2008 0:002008Regular1000 
670Nancy 1/1/2009 0:002009Regular1100 
670Nancy 1/1/2010 0:002010Regular1183 
670Nancy 2/1/2010 0:002010Regular1300 
670Nancy 1/1/2011 0:002011Regular1382 
670Nancy 1/1/2012 0:002012Regular1451 
670Nancy 1/1/2013 0:002013Regular1524 
670Nancy 4/1/2013 0:002013Regular1874 
670Nancy 1/1/2014 0:002014Regular1968 
670Nancy 1/1/2015 0:002015Regular2066 
670Nancy 1/1/2016 0:002016Regular2169 
670Nancy 3/1/2017 0:002017Regular2234 
670Nancy 2/1/2018 0:002018Regular2684 
670Nancy 3/31/2019 0:002019Bonus2684 
670Nancy 4/1/2019 0:002019Regular2791 
670Nancy 1/1/2020 0:002020Bonus5582 
670Nancy 2/28/2021 0:002021Bonus8038 
670Nancy 3/8/2022 0:002022Bonus8373 
670Nancy 3/1/2023 0:002023Bonus8373 
70Sara1/20/2004 0:002004Regular140 
70Sara1/1/2005 0:002005Regular175 
70Sara1/1/2006 0:002006Regular225 
70Sara1/1/2007 0:002007Regular325 
70Sara8/1/2008 0:002008Regular375 
70Sara1/1/2009 0:002009Regular420 
70Sara7/1/2009 0:002009Regular500 
70Sara1/1/2010 0:002010Regular538 
70Sara1/1/2011 0:002011Regular576 
70Sara1/1/2012 0:002012Regular605 
70Sara1/1/2013 0:002013Regular640 
70Sara1/1/2014 0:002014Regular690 
70Sara5/1/2014 0:002014Regular750 
70Sara1/1/2015 0:002015Regular800 
70Sara1/1/2016 0:002016Regular840 
70Sara3/1/2017 0:002017Regular865 
70Sara4/1/2019 0:002019Regular905 
70Sara1/1/2020 0:002020Bonus1358 
70Sara2/28/2021 0:002021Bonus1358 
70Sara3/8/2022 0:002022Bonus1358 
70Sara3/1/2023 0:002023Bonus2335 
       
Labels:
Message 1 of 3
389 Views
0
2 REPLIES 2
Jakinta
Solution Sage
Solution Sage

‎04-05-2023 11:11 AM

This could help.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZjNblNBDEZfpcq6Usb2/LJkh4RYwApVXQRUaKXSSilZ8PbcXG7CTK4/O900UnTkscdzPJm7u00JYXO7+bh7+v14M31oW0pbaq3chHfzN8fP07/PDz8Pz7v99ClK2NzfrkDectlyCOEEHj9fA9KWjlzquDRwiSwud1weuFzAQh2uBpVrHkeig8tCSweWCzBZYO3AOoJJjyguCHJcIrYObAM4byoEqdt8GjefSc+RXTBaxSHqQBrBbPUbcQfyCJbMBigdKCPY9OJEDxSzcyh2YBzBmJMBdmeKxjMlJZEBdk1OY5NLk6a3nMxk1zp0bJ33ry+Ht1kjRYzaXGKdNcLkH7hS7hqHQxevUotAU/XIdX3D1HGZYtbzm7Gua5j79AJHNb9/R5G7vWfpOWrLRtT56w/f9rvHp1/XVPR8MFbkVNNZxV2OYTwbUgDqyjgyIKdExfE4Ql2TJ7Rc1+UnB6xjBg8tIQHUXW+BUb1RcPIyIrHSa7NJ7HQKKGhxUcpmVMPrxLGYKDY7iUQTxW6nGMBxc+1Osdgo1jSliHrJEzVlZnjgHLQWgLqWp5azGbXrYBo7eBIcyDW5qGU0e0pwitXKFc4Jqai+3qTApDMrMrSvMyzOYJu//fp6eHv4cc2omG4JOkjb2dld54aLC0ZAoDMnBET0x0SEa/WmBFqrq/pUkk76vzHs+mDR56yD1QMLAP0BEU3QkjxIkl3SLo81HQqbpDEc7Dyt2dCELdK4+AduFoknA5OYq8V2Z05mTCx3lqLn6bs9LpfcS9JVu6Aecs0u05+RJxR7zssIW3et7fWWml5YR+utQc60+pk7vlfcbj7tXr7/WfKj6+S8Bq9zs85h2cVlsl9w7iU8FpVzHZn0dYrrVn2driGLXk//NSWYG3GFWVXQ0iNVwVthgWbPmHfuygZo3biXH7g6aNy3E0cNdF9TpnuvCvrX9JarARouDotrdNBQMWX17PsmZlFzXBqg61Uae5VzVUH3tQFw/s28NGP/ob5T0vvNs3cNou6hI+8qRT1QjrvP2Ex92e13c2YcbHFTDArlWJtKQhBWLzOEsD9FgaonQcHLwwI8vSb1kPvo8P/R+zKSIb60tIUCYemlkhGEhZcDKoThrAw7wvBVbmsoeVBJMBIWXMUlx3KrSk6u2GpeV891TIMlh34hSeuG8OSiQt7LsM6YWmGRKaH7vw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"Employee Full Name" = _t, #"Rate Card Date" = _t, Year = _t, #"Card Item Name" = _t, #"Card Item Amount" = _t]),
    #"Grouped Rows" = Table.Combine(Table.Group(Source, {"Employee Number"}, {{"A", each let t=Table.Sort(_,{{"Rate Card Date", Order.Ascending}}) in Table.RemoveColumns (Table.AddColumn ( Table.AddIndexColumn(t, "i",-1,1), "Increment", each   try  Number.From([Card Item Amount]) - Number.From(t[Card Item Amount]{[i]}) otherwise "" ), {"i"}), type table }})[A])
in
    #"Grouped Rows"
Message 2 of 3
340 Views
0
NawilatiMahmoud
Frequent Visitor
In response to Jakinta

‎04-05-2023 05:17 PM

it's not working, can you do it in a measure please

 

Thank you

Message 3 of 3
322 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

View All
Top Solution Authors
View All