how to find the minimum capacity day in the week and corresponding activity levels

[shneierl]

hi ,

i am still fairly new to bi/powerquery and have been able so far to do most of my reports using a bit of trial and error and google/stalking these forums my work still hasn't given my the powerbi desktop so any times i use dax or PQ it is from within the power add ins of excel. i know these are probably not quite as capable as the real product but you make do with what you've got access to. 

i am trying to take my daily data and say of the past 7 days on the day with the lowest capacity reported for group 1 was x and on this day they reported y amount of activity.  I had thought i had this done in my measure previously which was 

CALCULATE(MIN(table1[What is the current capacity?]),FILTER(table1,table1[week range]=[most recent complete week]))

 however this results in when not looking at a single site but either a regional or multisite level just extracting the lowest value of the sites being examined rather than the lowest combined value. 

i have made a very simple and cut down version of the data in the table below which has a few various sites with a parent grouping column and then an example of what my hoped for outcome would be based on this sample data. in the full data there are multiple sites per day per network and per region but i have streamlined this for simplicity but i asssume any advice given can be applied to a more complex dataset.

sample data

Relevant Date	Network	Region	What is the current capacity?	Total activity on relevant date
21/08/2021	S4	South east	500	480
21/08/2021	N1	North	500	500
21/08/2021	S1	South west	150	150
22/08/2021	S1	South west	200	140
22/08/2021	N1	North	500	450
21/08/2021	S6	South east	145	100
23/08/2021	S6	South east	200	45
20/08/2021	S6	South east	300	300
22/08/2021	S4	South east	450	450
21/08/2021	S5	South east	100	80
19/08/2021	S4	South east	750	600
20/08/2021	S4	South east	444	444
18/08/2021	S6	South east	25	5

expected outcome (network)

Network	What is the current capacity?	Total activity on relevant date
S4	444	444
N1	500	450
S1	150	150
S5	100	80
S6	25	5

expected outcome (region)

Region	What is the current capacity?	Total activity on relevant date
South east	775	605
North	500	450
South west	150	150

as you can see in the regional example as i am not certain my quick mock up showed the exact combination i wanted as i didn't sum each date and work out what was actually the lowest. but what i have done is look at the lowest capacity in the south east was on the with so the total capacity on this date was 775 with a total activity of 605. however in the final output i'd like it to create a hidden table that is say something like the below for south EAST daily totals and then only display to on the pivot or value the lowest row within the table created.

Relevant Date	What is the current capacity?	Total activity on relevant date
16/08/2021	100	99
17/08/2021	1000	800
18/08/2021	14444	785
19/08/2021	565	565
20/08/2021	280	200
21/08/2021	75	70

i am also not interested in mapping the lowest capacity and lowest activity if they are on separate days but would like to find out if the lowest capacity occurred twice in a week what would which day was the lowest activity and are we don't want to suggest your lowest activity was x and imply it came against a time when your capacity was y if they were not on the same date

hopefully the above makes sense but if not let me know and i'll do my best to explain further as i say normally i just do this via ghosting the forum so still probably need to learn how best to articulate my thoughts. also apologies if i have posted in the wrong subforum please let me know and i can repost where appropriate

[lbendlin]

@shneierl  Have you heard of the Filter Context concept yet?  It's an important part of DAX and impacts your measures.

Your sample data is missing the [week range]  column.  Can you please add that? Please also indicate what you mean by "week".

[shneierl]

Thanks for coming back to me sorry for the delayed response but i needed to get back to my office to see the dax codes in question.

My rough understanding of filter context in DAX (correct me if i'm wrong is as follows.  A measure in DAX will take the filters applied to the row on the pivot table or cube expression and return the relevant result from that. So if the measure is something like =min([daily capacity]) and the row is say s4 sub total it looks at all the daily capacities in S4 for the period queried and returns the lowest one. 

i will edit my sample data shortly but i din't include the week groupings to make it easier as a sample (the real table is 91 columns wide and 10s of thousands of rows plus supporting tables within the model. the way i created my week groupings in the model was as follows. i created a week number column that would return the was 

=(weeknum[relevant date],21)

 this returned the weeks starting on Monday using the ISO system. i then did the same in my date table and looked up the lowest day (ie the corresponding Monday) for that week number.

=CALCULATE(MIN(calander[week number]),FILTER(calander,calander[date]=Table1[relevant date]),FILTER(calander,calander[Year]=YEAR(Table1[relevant date]))

as you can see i currently i am using the the year function to ensure i pick the right date as the items i am interested in on the pivots are only the most recent 6 weeks but the dataset and therefore date table starts from march last year. i had originally hoped that we'd stop needing this data before the end of this year but that is looking unlikely so if you have suggestions for how i can get this info out and not have to filter by year as when i come to jan 1st my week range will be two part weeks for the period that straddles the new year.

originally as the code above shows my weeks were simple Monday-Sunday. however due to when the adjustments are posted to the data and the change to a weekly report versus daily we have moved to a Thursday to Wednesday week. the principal is the same as the above just  i created a second date column that combines the two formulas to use the weeknum((relevantdate],14) as one of the filters. i have then also created week end columns by just taking the week start points and adding 6. this allows me to check my table to see if the week has had a completed return yet (had returns for everyday more important when i was doing daily reports so i would update the dataset with partial weeks)

my current min activity measure is as follows, i know it is wrong as i have effectively fixed the week in the filter as the latest complete test week is the max week start date where the test week is listed as having 7 unique dates in it. so i think this means when i filter for several dates the latest week is still the same unless i add dates to rows/columns to provide context to the measure. 

most recent complete test week:=CALCULATE(MAX(table1[test week range]),FILTER(table1,table1[test complete week]="yes"))

minumn capacity in week:=CALCULATE(MIN(table1[current capacity]),FILTER(table1,table1[test week range]=[most recent complete test week]))

lowest capacity activity:=CALCULATE(MIN(table1[Total activity]),FILTER(table1,[test week range]=[most recent complete test week]),FILTER(table1,table1[current capacity]=[minumn capacity in week]))

i have bodged a solution to this in my cubevalues as if row = region/network =sumifs the lowest values of that area. However i know this is wrong due to potential of the lowest activity not falling on the same day etc but only i know this the end users can't tell this so odds of anyone ever spotting it were slim. 

i hope the above helps i'll expand on sample data later on butlet me know if you have further questions