calculate estimated service date
I'm working on the following use case: It involves a service desk and the estimated date that a ticket will be processed. At any certain day, there will be an X amount of capacity per team to process tickets (eg 20 hrs on 2022-12-8, 15 on 2022-12-9 etc). Each ticket contains an estimated effort to complete it. The challenge is to estimate when a ticket will be picked up by the service desk based off the ticket rank and the available capacity. Tables: Capacity Team Date Capacity in hours a 2022-12-8 20 b 2022-12-8 30 a 2022-12-9 15 b 2022-12-9 25 Tickets (filtered status = Open) rank Assigned to Team Estimated effort in hours 1 a 10 2 a 10 3 b 30 4 a 10 5 b 10 Capacity table is connected to date table and team table. Ticket table is only connected to team table (since I need to calculate the estimated date still) Goal: In my example, tickets rank 1 and 2 will be processed on 2022-12-8 by team a, and ticket rank 4 will be processed on 2022-12-9. I've come as far to calculate on a per day basis (=capacity 8 hours per day) using Current Date as a starting point in the following measure: Estimated date = --calculate estimated effort in days var _remwork = DIVIDE (CALCULATE(SUM(Tickets[Estimated Effort]), FILTER(ALL(Tickets),Tickets[Rank]<SELECTEDVALUE(Tickets[Rank]))),8,0) var _currentDate = Today() var _networkingdays = FILTER(ALL(Datum),Datum[Datum]>_currentDate && Datum[Weekday] = "Yes") var _Datecomplete = TOPN(ROUND(_remwork,0),_networkingdays,Datum[Datum],asc) var _result = MAXX(_Datecomplete,Datum[Datum]) return _result I'm struggling to compare the actual capacity per given date with the cumulative estimated effort of all tickets. That means that in the first step, calculating the effort, I somehow have to incorporate the available capacity and when the cumulative estimated effort exceeds this for any given day, it will add a day to the estimated date and so on. What I can't seem to wrap my head around is that the capacity has assigned dates, but the tickets have not, making it hard to compare it on that dimension. Any idea is welcome (DAX, Power Query, both).Find max for each group of rows
Hello. I have the problem - how to get column 3 according to Column 1, 2, and the value column (table is below) For the locations and names, I have to detect the max value that I will use in further calculations Not sure can it be done in M Query or DAX? Thanks Nice hot greeting from Croatia
How to create a rank column using a boolean value column, by occurence of 'TRUE' in ordered table?
I have a time indexed table with a `Group`, a `Category` , and a `event_bool` . I need to plot stuff against the boolean variable, because whenever `TRUE` value occurs it is an `event` in the timeline that needs to be analyzed one against the other. After the ranking is done, I also want to fill the ranking in a way that only the `Category` variable with value `foo` gets the rank value. Thus the event time range is "expanded" out based on `Category` variable. The last two columns are expected outputs. Edit: Ranking is to be done only when Cateogry='foo' Edit: sample pbix file: https://drive.google.com/file/d/1mPlsGJ1YR88YfyKkIgYBTIZuGyBDT38S/view?usp=sharing I have previously done `Expected_ranking_output` in python, and was planning to plug in the python script with power query; however, I read in the docs that you have make the dataset public if you want to python with power query (bemused by this). Not sure if my organization will allow that. Group Time_Index Category event_bool Some_Plotting_Value_Col Expected_ranking_output Expected_Filled_Output A 1 other FALSE 0.929 0 0 A 2 foo FALSE 0.929 0 1 A 3 foo FALSE 0.209 0 1 A 4 foo FALSE 0.191 0 1 A 5 foo TRUE 0.071 1 1 A 6 foo TRUE 0.507 1 1 A 7 foo TRUE 0.101 1 1 A 8 foo TRUE 0.661 1 1 A 9 foo FALSE 0.936 0 1 A 10 foo FALSE 0.611 0 1 A 11 foo TRUE 0.397 2 2 A 12 foo TRUE 0.677 2 2 A 13 foo TRUE 0.407 2 2 A 14 foo FALSE 0.473 0 2 A 15 foo FALSE 0.411 0 2 A 16 foo FALSE 0.012 0 2 A 17 bar TRUE 0.080 0 0 A 18 bar TRUE 0.149 0 0 A 19 bar TRUE 0.748 0 0 A 20 bar TRUE 0.812 0 0 B 1 foo FALSE 0.734 0 1 B 2 foo FALSE 0.917 0 1 B 3 foo TRUE 0.734 1 1 B 4 foo TRUE 0.755 1 1 B 5 foo TRUE 0.854 1 1
Group table in terms of date column and specific value of another column
Hello all, I have a problem which sounds so easy but as I'm a newbie I can't manage it and need your expertise to help me please: As a sample you can have a look at the table below: I need to remove all the rows which have not Main Category value on their date (in this example I should remove rows 9,10,11), The main category value here is the beginning point for my process per day and if it does not exist in a day, I should remove all the other records on that specific date or ignore them could you please let me know how can I make a DAX code or M-query to fix it? as my data are thousands or millions, needs to have the fastest one. Many thanks
Expand Dates in Large Data Set - PowerQuery and Dax
I have a large data set in a proper star schema. However, i'm struggling with some reports out of it. I'll explain what i'm currently doing, and hopefully someone can tell me a better way (or more elegant) way to accomplish something similar. Data set: There's many more tables that have proper relationships (Aprox 30 more), but for the purpose of this exercise, that's all we need. Most of the work happens in the ResourceActualDetail table it looks like this (with some columns deleted): ResourceAllocationId AllocationPercentage EndDate StartDate 794 1 4/1/2018 0:00 2/25/2018 0:00 795 1.15 3/4/2018 0:00 2/25/2018 0:00 795 1.0875 3/11/2018 0:00 3/4/2018 0:00 795 1 4/1/2018 0:00 3/11/2018 0:00 796 1.05 3/4/2018 0:00 2/25/2018 0:00 797 0.725 3/4/2018 0:00 2/25/2018 0:00 1531 1 3/11/2018 0:00 2/25/2018 0:00 1721 1 4/1/2018 0:00 2/25/2018 0:00 Desired Output: ResourceAllocationId AllocationPercentage EndDate StartDate Date 794 1 4/1/2018 0:00 2/25/2018 0:00 2/26/2018 0:00 794 1 4/1/2018 0:00 2/25/2018 0:00 3/4/2018 0:00 794 1 4/1/2018 0:00 2/25/2018 0:00 … 794 1 4/1/2018 0:00 2/25/2018 0:00 4/1/2018 0:00 Notice the 7 day increments. And the same for every ResourceAllocationID so that I can graph it like so: (error in Graph dates, should be with 7 day increments). Where there is a different line for each Resource (connected through relationships to ResourceAllocation and ResourceActualDetail). Even if we can get it to a point where we can do that for each ResourceAllocationId I can figure out the rest 🙂 Current Approach: These data tables are quite large already(~1M rows) , and my approach only has bloated that. Trying to do everything as close to the source, this what I came up with: Let Source=PowerBI.Dataflows(null), ... #"Changed Type" = Table.TransformColumnTypes(ResourceActualDetail1,{{"EndDate", type number}, {"StartDate", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Numbers([StartDate],([EndDate]-[StartDate])/7,7)), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type datetime}, {"EndDate", type datetime}, {"StartDate", type datetime}}) in #"Changed Type1" Essentially, I change the StartDate and EndDate fields to numeric Values and then I calculate the beginning of each week before i switch back all those values to Date type. This part of the query doesn't fold, so I cannot take the advantage of the PowerFlow to do it. It also makes each refresh very slow (should be refresed multiple times a day). Any suggestions or ideas?? Obviously looking for the most efficient way to achieve this so that it won't take many resources (I do have a premium instance). Thanks!Find repeat values based on criteria
I’m hoping you all might be able to help me. I’m fairly experienced in Power BI but can’t figure this issue out. I have a list of dates/times and phone numbers and departments (and some other data as well). What I am trying to do is figure out if a certain department is better at answer questions the first time, resulting in the fewest call backs. For example, let’s say phone number 1234567890 called and was routed to Department Y. Then a week later they called again, and the call was routed to Department X. I want this to be counted as a repeat caller for Department Y (because they originally contacted Department Y first, so it’s department Y’s “fault” they are calling again). Happy to clarify more if needed. I also don’t care if this is written with Dax or M. I also just made a breakthrough where I was able to have a running count of whether the call was 1st, 2nd, 3rd, etc. Time the phone number has called. So now I just need something that says: If the number is greater than 1, then find the first instance of the number and return which department answered the call. Thank you in advance.DAX relative time function to count number of instances between specific times
Hi, I have some data in the following format: Incident Number Date Occurred Incident Name 143 1/1/2001 12:00 ex 1 361 1/2/2001 14:00 ex 2 I am trying to count the number of incidents that have a [Date Occurred] within a certain time frame relative to the current date. I want the number of incidents with a [Date Occurred] between 7 days ago after 12:00 PM to today before 12:00 PM. To give an example: if it is Thursday today, I would like to count the number of incidents with a [Date Occurred] between LAST thursday after 12:00 PM and 12:00 PM this Thursday (today). Ideally, I would like a measure to get this count. Thanks!
