Summarizing Hours by Work Order Order Date and Need by Date in the past, to see history trend
Hi all, I have two data tables 1. Work Order Table (4801) with Order Date and Need by Date 2. Estimated Hours by step (3112) They are related by WO number with Many to Many connection. What I am trying to get is to see how my backlog (overdue WO hours) and workload (due WO hours) were build in the past. From week to week. This is DAX formula I use Due WR = var CheckOrderDate = CALCULATE( SUM(F3112[EstimatedHours]), Filter( ALL('F4801_Work Orders'[Order Date for Measure]), 'F4801_Work Orders'[Order Date for Measure] <= MAX('Calendar Table'[Periods with Today])), USERELATIONSHIP('F4801_Work Orders'[Order Date], 'Calendar Table'[Date]) ) var DueHours = CALCULATE( SUM(F3112[EstimatedHours]), Filter( ALL('F4801_Work Orders'[NBD for Measure]), 'F4801_Work Orders'[NBD for Measure] <= MAX('Calendar Table'[Periods with Today])) ) return IF(CheckOrderDate = BLANK(), BLANK(), DueHours) Calendar Table range is from 2013 up to 2031. Ive added addiotnal columns into calendar with Yearweek (Periods with Today) number for measure as "201301" to be able compare it with F4801 NBD or Order Date (Order Date for Measre) in same format (Whole Number). However, Due WR measure returns me Blank values for all and each week but in Subtotal it gives correct value. Var CheckOrderDate returns value only for Year-Week it was created but not for all later/earlier weeks. Expired WR measure works correctly Expired WR = CALCULATE( SUM(F3112[EstimatedHours]), Filter( ALL('Calendar Table'[Week_number]), 'Calendar Table'[Week_number] <= MAX('Calendar Table'[Week_number]) )) It retruns values exactly from the Year-Week it past due. All relations are as follows F3112 - F4801 WO # many to many 4801 NBD and calendar table (Date) 4801 Order Date and calendar table (Date) both not active, to be able USERELATIONSHIP in measure F3112 and WeekData(Dictionary table) by Primary Craft I am struggling with it for a week, and had no other ways than here. I tried to provide all information, if something missing pls let me know.
