Creating a chart for rolling figure in date format
Hi all, I have built a employee turnover metric that looks at average headcount and total leavers in a 12 month timeframe. To get the % figures I have built a Date Table that when I slide the start and end period of each month it returns me the complete breakdown of the data I need. Rolling Turnover % (Total) = DIVIDE([Rolling Leavers (Total)],[Avg Monthly Headcount (12M)],0) However, when reporting on trends it makes it difficult as I have to move the date table periods each time to the 12 month outlook I need. For example for December 2024 I need to put 01/01/2024 - 31/12/2024, for January 2025 it would be 01/02/2024 - 31/01/2025. The Date Table is linked to my main staff file, which has each month of the period 01/2024, 02/2024 etc, and then linked also to my exits table that has the last day per individual. This gives me exactly what I need but it would be very helpful to try and investigate if there is a way to add to this to be able to build some charts over time to better understand trends. Appreciate I may be lacking a lot of info still required, will try my best to share anything that is not clear. Kind regards,
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.
