Forum Discussion
Identifying first rows based on a summed value
Hi all, one more problem. I have a table with a date column, # of hours worked, and our normal price per hour. Usually we charge '$X' per hour but for one client we charge them '$Y' for the first 10 hours of work every month and '$X' for everything after that.
Should I attempt to do this in DAX or Power Query? How can I isolate the first time entries every month until they aggregate to the 10 hr limit. Do I need something like a conditional column that says at the start of every month charge '$Y' for the first 10 hours and then charge '$X' for anything after that?
Any thoughts would be appreciated
Hi mmikemcgregor1 ,
Here are the steps you can follow：
1. In Power query. Add Column – Index Column – From 1.
2. Create calculated column.
Rankx = RANKX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),[Index],,ASC)Sum = SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Rankx]<=EARLIER('Table'[Rankx])),[Work Hours])Need to change Rate/Hr = IF( 'Table'[Sum]<=10,"150","Stay at Normal Rate")3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
5 Replies
Little bit difficult to know without seeing your data.
It sounds to me like something you could do in dax, do you have sample data to share?
Sorry, should have included a sample data set. The table below is how the data appears in our data pull. So I need to reclassify the first 10 hours each month(regardless of who worked them) to $150/hours and the rest of the time in that month stays at the normal rate.
Date Title Work Hours Rate/Hr Total Amt Need to change Rate/Hr 7/1/2022 Manager 1 300 300 150 7/1/2022 Apprentice 2 200 400 150 7/2/2022 Manager 4 300 1200 150 7/2/2022 Apprentice 1 200 200 150 7/3/2022 Manager 1 300 300 150 7/3/2022 Apprentice 1 200 200 150 7/4/2022 Manager 3 300 900 Stay at Normal Rate 7/4/2022 Apprentice 6 200 1200 Stay at Normal Rate 7/5/2022 Manager 1 300 300 Stay at Normal Rate 7/5/2022 Apprentice 2 200 400 Stay at Normal Rate 7/6/2022 Manager 3 300 900 Stay at Normal Rate 7/6/2022 Apprentice 2 200 400 Stay at Normal Rate 8/1/2022 Manager 2 300 600 150 8/1/2022 Apprentice 5 200 1000 150 8/2/2022 Manager 3 300 900 150 8/2/2022 Apprentice 4 200 800 Stay at Normal Rate 8/3/2022 Manager 1 300 300 Stay at Normal Rate 8/3/2022 Apprentice 5 200 1000 Stay at Normal Rate 8/4/2022 Manager 3 300 900 Stay at Normal Rate 8/4/2022 Apprentice 1 200 200 Stay at Normal Rate
Hi mmikemcgregor1 ,
See an example below. If you know that the customer is getting a rate for the first 10 hours, then the best thing to do is a base rate calculation less the 10 hours and then those 10 hours at the other rate and add them up. Again as vanessafvg mentioned hard to figure without seeing data. This may help you get started though.
Hope this helps
Did I help you today? Please accept my solution and hit the Kudos button.
Hi mmikemcgregor1 ,
Here are the steps you can follow：
1. In Power query. Add Column – Index Column – From 1.
2. Create calculated column.
Rankx = RANKX(FILTER(ALL('Table'), YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),[Index],,ASC)Sum = SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&& MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Rankx]<=EARLIER('Table'[Rankx])),[Work Hours])Need to change Rate/Hr = IF( 'Table'[Sum]<=10,"150","Stay at Normal Rate")3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
