Column sum issues
Hi, I've looked at the various solutions and none seem to work. It's the standard issue, that the sum at the bottom, or categoery, is way above what it should be. I've tried the hasonefilter etc, but still no joy. I'm using two tables, though not sure if this is the issue, so it's a rate in one and a quantity in another, multiplying to give an overall total. code costs = if(HASONEFILTER(Data[Job Task Description]),SUM(Data[Quantity])*SUM('std costs'[Unit Cost (LCY)]))
How to get sum of units in user-defined date period?
Hi, you guys have helped me out before, hoping you can do so again! 😊 I have a table that contains the following fields: ClientID Supplier StartDate EndDate WeeklyUnits 1 ACMECo 06/06/2019 10 2 BillyOnions 01/01/2019 17/01/2019 5 3 FredBloggs 01/02/2019 03/03/2019 10 4 ACMECo 31/12/2018 12/05/2019 20 I have a DateKey table where I have NOT created a relationship to any of the date fields in other tables. I use a date slicer in the report to produce a graph of units per week by supplier. This is used for giving a trend on what a provider’s planned unit capacity was at the beginning of each week. To get this I use the following measure in conjunction with filtering the visual to just show data for Mondays: WeeklyUnitSnapshot= CALCULATE( SUM('MyTable'[Weekly Units]), FILTER( 'MyTable', [START DATE] <= FIRSTDATE(DateKey[Date])), FILTER( 'MyTable', [END DATE] = BLANK() || 'MyTable'[END DATE] >= LASTDATE(DateKey[Date])) ) So far, so good, but I also need to track what was actually delivered by suppliers in the date period defined by the report user. eg. "How many units did AcmeCo provide between 1st of Jan and 1st of Feb?" What I need to achieve is calculate the number of days the order was open within the user selected dates, taking into account the start and end dates in the table, then multiplying the number of days by weekly units and then dividing by 7. I’ve tried various ideas but can’t fathom how to get this working, I tried another measure but it was ignoring the user-selected date range and just using the first and last dates in my DateKey table. Any help would be greatly appreciated!
