Need help with creating subset table using filter()
Hi everyone, In a bit of a pickle here. i have been trying to create a subset of my main table using selectedvalues() from a slicer. mth | ID | Savings 31/01/2023 | A123 | 50,000 28/02/2023 | A123 | 60,000 31/01/2023 | B456 | 35,000 28/02/2023 | B456 | 30,000 31/01/2023 | C789 |15,000 measure start_mth = format(SELECTEDVALUE(onboarding_base[mth]),"DDMMYYYY") i tried the simple way of track_end_filtertable = Filter(FILTER(onboarding_base,onboarding_base[mth]=[start_mth]),onboarding_base[period_idmmmyy]=[start_mth]) This doesnt work. I also tried to break it down the measure further as follows but it failed too. Measure track_start_min_period_id = var v_m = format(SELECTEDVALUE(onboarding_base[min_period_id]),"DDMMYYYY") var v_year = format(right(v_m,4),"") var v_day = format(left(v_m,2),"") var v_mth = format(left(right(v_m,6),2),"") var v_date_sel = datevalue(v_day&"/"&v_mth&"/"&v_year) return DATEVALUE(v_date_sel) table track_start_filtertable = var v_date = [track_start_min_period_id] return filter(filter(onboarding_base,onboarding_base[min_period_id]=[track_start_min_period_id]),onboarding_base[period_id]=[track_start_min_period_id]) Lastly i also broke the date down and used date (year,month,day). Again.. no success. However in all instances, if i were to hard code the dates, the subset filtered table is created successfully. Im at wits end! appreciate the help!
Calculating number of business day excluding Country Holidays
Hi, I am trying to figure out how to calculate the number of business days (Age of Order) between the date an order was submitted and today (using a table with list of Holidays per country) Here are my tables: COUNTRY TABLE HOLIDAYS TABLE ORDER TABLE I am able to add a column by invoking the following custom function – this will exclude week-ends and also any dates in the Holidays table. = (StartDate as date, Holidays as list) => let EndDate = DateTime.Date(DateTime.LocalNow()), // Providing for logic where EndDate is after StartDate Start = List.Min({StartDate, EndDate}), End = List.Max({StartDate, EndDate}), // Switch sign if EndDate is before StartDate Sign = if EndDate < StartDate then -1 else 1, // Get list of dates between Start- and EndDate ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)), // if the optional Holidays parameter is used: Keep only those dates in the list that don t occur in the list of Holidays; // otherwise continue with previous table DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )), // Select only the first 5 days of the week // The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, 1) < 5 ), // Count the number of days (items in the list) CountDays = (List.Count(DeleteWeekends) * Sign) - 1 in CountDays The piece that I can’t seem to figure out is how to only apply holidays matching the country of the order. Thanks for you help
I need Help for my DAX. If the Cycle time is during The MCO how to calculate the days During MCO
Hi All, I have 1 impediment to calculate the Days During MCO on my queries. This is to calculate how many days are there by using start date MCO and End date MCO. Below is the example of my queries. For your reference below is the output that i wanted including the start date and the end date of the MCO i include how i do calculate the Cycle time . How can i use queries or using measure to auto populate the Days During MCO for my Data. Hope you all can help me. Best regards Thanks Source Table Item Incurred Resolved Cycle Time Days During MCO Cycle Time Exclude MCO A 8/29/2021 9/14/2021 17 B 8/28/2021 9/14/2021 18 C 8/26/2021 9/14/2021 20 D 8/25/2021 10/10/2021 47 E 8/25/2021 10/10/2021 47 F 8/24/2021 10/10/2021 48 G 8/23/2021 9/14/2021 23 H 8/23/2021 11/30/2021 99 I 8/20/2021 11/16/2021 89 J 8/20/2021 9/14/2021 26 K 8/19/2021 9/14/2021 27 L 8/18/2021 9/14/2021 28 M 8/18/2021 9/14/2021 28 N 8/16/2021 9/14/2021 30Number of projects that started minus project that ended
Hello, I need to calculate the number of projects that started on a particular date minus the number of projects that ended. In my dataset I have 1,023 projects that all have a start date and an end date, as seen in the image below. I gave every project a value of 1 in the table ValueProjects, so I could calculate the number of projects that started and ended on particular months. By using a table with the values: data.start_date(Months) and ValueProjects and a table with the values: data.end_date(Months) and ValueProjects I could see how many projects were started and how many ended per month, as seen below. The problem that I have is that I don't know how to make a calculation that calculates the projects started minus the projects ended. Is there a sollution for this?
