I have the following problem and, after searching on this forum & internet, I cannot find my way out...
For each time period (by hour here), I have a measure to define the number of trucks who arrived in a warehouse [12_Actual Load] and the available capcity, ie the max number of trucks which can be undocked during the period [13_ActualCapacity].
The number of trucks entering/leaving the queue is then [31_In/Out waiting trucks] = [12_ActualLoad] - [11_ActualCapacity].
I would like a measure to calculate the number of trucks waiting in the queue at the end of each time period.
For the first time period (1), it is Queue(1) = Max( 0 , [31_In/Out waiting trucks](1) )
For the next time periods, Queue(n+1) = Max( 0 , [31_In/Out waiting trucks](n+1) + Queue(n)
I am repliying so that I can keep track of this very interesting Q. I was going to suggest a cumulative total column of 31 (that for my novice skills was complex) but the max 0 formula points out that you can't go negative and therefore need to account for it which my suggestion would not have handled. So hopefully a hero DAXer comes along.
Thanks a lot for your reply @Greg_Deckler . I do not think that this can be calculated with a cumulative total, because if I'm in an "overcapacity" situation, then the queue will stay to zero and I will kind of 'waste' capacity (ex: from 09:00 to 13:00).
But once that there are more trucks arriving (actual load) in a period than 'actual capacity', the queue will start to grow. And enventually decrease up to zero on periods when there will be more 'capacity' than 'load'.
Unfortunately, I do not have the data (I get a file "Raw Data" with all the trucks for a week and then aggregate them by timeslot to calculate the "Actual load". And I get the "Actual Capacity" from another data source.
I tried to apply your method to a merged table after having grouped my raw data by timeslot to have "actual load" and "actual capacity" by timeslot and to start with the same table than your first printscreen. It works on the preview but I need ~10min to load a preview and then when I "close and apply", it get stucks in "Apply query changes" and keeps calculating up to 5 GB from my file (both files "raw data" and "capacity" are below 80kb)
Any idea if I could do the same type of calculation using measures & calculated columns ?
>>It works on the preview but I need ~10min to load a preview and then when I "close and apply", it get stucks in "Apply query changes" and keeps calculating up to 5 GB from my file (both files "raw data" and "capacity" are below 80kb)
For the performing reason, I don't recommend you use recursive calculation function on huge amount of records.
I'd like to suggest you direct calculate the result in excel instead of use recursive calculation in power query.(in excel you can got the row content from specify cell, it cost less than which recursive calculation spend, so it will be suitable for your scenario)
Then import these data to power bi.
Community Support Team _ Xiaoxin Sheng If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
For learning resources/Release notes, please visit: | |