dax max date within date range
6 Topics
last date not shown correctly
Hello Dear Team, Got an issue with displaying the last date I have a table with startTime endTime and WeekRange startTime endTime 2024-07-02 2024-07-09 2024-07-09 2024-07-16 2024-07-16 2024-07-23 2024-07-23 2024-07-30 2024-07-30 2024-08-06 2024-08-06 2024-08-13 and I have added new column named WeekRange to show a Week Range in this format: 06-08-2024 to 13-08-2024 02-07-2024 to 09-07-2024 30-07-2024 to 06-08-2024 WeekRange = FORMAT([startTime].[Date], "DD-MM-YYYY") & " to " & FORMAT([endTime].[Date], "DD-MM-YYYY") Now I want to show in a Card visual only the last week range, On visualizations, when selecting in the Fields Last WeekRange it shows the 30-07-2024 to 06-08-2024 but not the correct one 06-08-2024 to 13-08-2024 Do you know how do I fix this ?Solved690Views0likes3CommentsDAX calculated columns to create Date Tables
Hi All, I need to create a Date Table [Data Table] using any possible DAX measures or DAX calculated columns. I need the output in the below expected format.Please suggest. so I basically want to show current month, year, previous month along with the month start and end dates. I want it for the whole year for 2021,2022 and 2023..Please suggest Current Month Year CurrentMonthStart CurrentMonthEnd Previous Month PreviousMonthStart PreviousMonthEnd January 2022 01-01-2022 31-01-2022 December 01-12-2021 31-12-2021 February 2022 01-02-2022 28-02-2022 January 01-01-2022 31-01-2022 March 2022 01-03-2022 31-03-2022 February 01-02-2022 28-02-2022 April 2022 01-04-2022 30-04-2022 March 01-03-2022 31-03-2022 May 2022 01-05-2022 31-05-2022 April 01-04-2022 30-04-2022Solved1.9KViews0likes5Comments
Help required-Value from another table on the bases effective date
Hi, I have 2 tables, they are both join with product( many to many relationship). Order Order No Orderdate Product 1 01/01/2021 A 2 02/01/2021 B 3 01/02/2021 A 4 02/02/2021 B 5 02/02/2021 A 6 03/02/2021 B 7 25/02/2021 A 8 26/02/2021 A 9 07/02/2021 B 10 08/03/2021 A Cost Cost % EffectiveDate Product 1.1 01/01/2021 A 1.4 01/01/2021 B 1.5 01/02/2021 A 1.8 15/02/2021 A 1.9 01/03/2021 A 2 02/02/2021 B Need the result like this Order Order No Orderdate Product Cost% 1 01/01/2021 A 1.1 2 02/01/2021 B 1.4 3 01/02/2021 A 1.1 4 02/02/2021 B 2 5 02/02/2021 A 1.5 6 03/02/2021 B 2 7 25/02/2021 A 1.8 8 26/02/2021 A 1.8 9 07/02/2021 B 1.5 10 08/03/2021 A 1.9 If write custom colum to get cost% in order table this its give error of multiple values VCost% =LOOKUPVALUE('Product'[Cost%],'Product'[Product],'Order'[Product]) Or no result Vcost% =CALCULATE(SELECTEDVALUE(Product[Cost%]),Orderdate>=MINX(Product,Product[EfffectiveDate])&& OrderDate <=MAXX(Product,Product[EffectiveDate])) OR I have tried this also but no result Cost1% = Var MinEffDate = CALCULATE(MINX('Cost','Cost'[EffectiveDate]),FILTER('Cost','Cost'[Product]=('Order'[Product]))) Var MaxEffDate = CALCULATE(MAXX('Cost','Cost'[EffectiveDate]),FILTER('Cost','Cost'[Product]=('Order'[Product]))) Return CALCULATE(SELECTEDVALUE('Cost'[Cost%]),'Order'[Orderdate]>=MinEffDate &&'Order'[Orderdate] <=MaxEffDate,FILTER('Cost','Cost'[Product]=('Order'[Product]))) Can you please how can I achieve this result. ThanksSolved1.1KViews0likes4CommentsDAX to calculate the count of the flags
Hi All, I have a table named student with columns DateBooked,ParticipantName and NoBookingCheckFlag. This data is corresponding to the students reservation for a school program. The Date Booked column shows the booking date of the ticket,ParticipantName is the name of the student and NoBookingCheckFlag column shows those students who attended the program without reservation so if the flag is set to Yes then they attended the program without reservation and if flag is set to No the students attended the program with reservation. Expected results : I need to create a calculated measure column to find the remaining columns OverAllCount(with/without reservation,count(with reservation) and %ge ratio corresponding to the respective booking dates. % age ratio = 1- count(with reservation)/OverallCount(with/without reservation) Can you please suggest suitable DAX to handle the expected results? Input source : Date Booked Participant Name NoBookingCheckFlag OverallAllCount(with/withoutreservation) Count(WithReservation) %geRatio 15.09.2021 Tom Yes 16.09.2021 Tom Yes 15.09.2021 John Yes 16.09.2021 John No 20.09.2021 Mary Yes 21.09.2021 Mary No 17.09.2021 Mary No 13.09.2021 Elizabeth Yes 14.09.2021 Elizabeth Yes 09.09.2021 Mario Yes 10.09.2021 Mario Yes 17.09.2021 Mario No 20.09.2021 Mario No 21.09.2021 Mario Yes Kind regards SameerSolved3.5KViews0likes3Comments
calculate Dates based on conditions
Hello everyone !! Please help me on this calculation, I am stuck on this from last 2 weeks. I am trying to populate Final Dates column based on Final Dates Notes. Below is the data and description of the columns. Final Dates column is based on Minimum dates taken from Actual Stage 1 completion date and Lifecycle Stage Completion Date for BU. logic is : 1. for Stage 1, populate Min(Actual Stage 1 Date) and for remaining Stage, populate Min(Lifecycle Stage Completion Date). For Example : Final Dates for Lifecycle Stage 1 date is Min(Actual Stage 1 Date) and for final dates Stage 2 is Min(Lifecycle Stage Completion Date) of Stage 1. Use Case Lifecycle Stage Lifecycle Stage Completion Date Actual Stage 1 Date Final Dates Final Dates Notes SWIM Stage 1 9/1/2018 8/1/2018 8/1/2018 Date from Actual Stage 1 Date SWIM Stage 2 8/1/2018 9/1/2018 Date from SWIM Stage 1 Complete BU = Business Unit Use Case = group of conditions Lifecyscle Stage = Stage for BU for each Use Case Current Stage = Stage where currently BU is sitting in current date Max Stage = Stage where BU reached across all the use case Lifecycle Stage Completion Date = the date when a Lifecycle stage completed Actual Stage 1 Date = Dates when first Stage got completed Final Dates = Minimum Dates from Lifecycle Stage Completion Date and Actual Stage 1 Date (this is what I have to calculate) Final Dates Notes = Conditions for each row in Final Dates on how it should populate BU Use Case Lifecycle Stage Current Stage Max Stage Lifecycle Stage Completion Date Actual Stage 1 Date Final Dates Final Dates Notes BU1 Assurance Stage 1 Stage 1 Stage 5 8/1/2018 8/1/2018 Date from Actual Stage 1 Date BU1 Assurance Stage 2 Stage 1 Stage 5 8/1/2018 9/1/2018 Stage not in progress. Date should match Stage 2 for rest of Use Cases BU1 Assurance Stage 3 Stage 1 Stage 5 8/1/2018 7/20/2019 Stage not in progress. Date should match Stage 3 for rest of Use Cases BU1 Assurance Stage 4 Stage 1 Stage 5 8/1/2018 7/22/2019 Stage not in progress. Date should match Use for rest of Use Cases BU1 Assurance Stage 5 Stage 1 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Engage for rest of Use Cases BU1 Assurance Stage 6 Stage 1 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Adopt for rest of Use Cases BU1 Assurance Stage 7 Stage 1 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Optimize for rest of Use Cases BU1 Assurance Stage 8 Stage 1 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Advocate for rest of Use Cases BU1 SWIM Stage 1 Stage 2 Stage 5 9/1/2018 8/1/2018 8/1/2018 Date from Actual Stage 1 Date BU1 SWIM Stage 2 Stage 2 Stage 5 8/1/2018 9/1/2018 Date from SWIM Stage 1 Complete BU1 SWIM Stage 3 Stage 2 Stage 5 8/1/2018 7/20/2019 Stage not in progress. Date should match Stage 3 for rest of Use Cases BU1 SWIM Stage 4 Stage 2 Stage 5 8/1/2018 7/22/2019 Stage not in progress. Date should match Use for rest of Use Cases BU1 SWIM Stage 5 Stage 2 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Engage for rest of Use Cases BU1 SWIM Stage 6 Stage 2 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Adopt for rest of Use Cases BU1 SWIM Stage 7 Stage 2 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Optimize for rest of Use Cases BU1 SWIM Stage 8 Stage 2 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Advocate for rest of Use Cases BU1 NDO Stage 1 Stage 5 Stage 5 10/1/2018 8/1/2018 8/1/2018 Date from Actual Stage 1 Date BU1 NDO Stage 2 Stage 5 Stage 5 8/30/2019 8/1/2018 9/1/2018 Date from SWIM Stage 1 Complete BU1 NDO Stage 3 Stage 5 Stage 5 9/30/2019 8/1/2018 7/20/2019 Date from SPA Stage 2 Complete BU1 NDO Stage 4 Stage 5 Stage 5 3/5/2020 8/1/2018 7/22/2019 Date from SPA Stage 3 Complete BU1 NDO Stage 5 Stage 5 Stage 5 8/1/2018 3/5/2020 Date from NDO Use Complete BU1 NDO Stage 6 Stage 5 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Adopt for rest of Use Cases BU1 NDO Stage 7 Stage 5 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Optimize for rest of Use Cases BU1 NDO Stage 8 Stage 5 Stage 5 8/1/2018 3/5/2020 Stage not in progress. Date should match Advocate for rest of Use CasesSolved2.4KViews0likes8CommentsDAX Formula to find last day worked within a week
I am trying to develop a formula to calculate the last working day within a work week for a group of employees. A work week runs from Monday to Sunday. The group as a whole will work 5-7 days per week, where an individual may work any number of days during the week. The Last Work Date will be the same for all employees who work during a work week regardless of the number of days worked during the week. My table is represented below. I need to calculate the last date worked for the group of each week. I have filled in the Last Work Date column with the desired results. Please note, in the example below RC did not record production on 6/27/20, but the Last Day Worked is 6/27/20 based on production dates of the other employees. Employee Week Beginning Date Production Production Date Last Work Date of Week RC 6/8/2020 5 6/13/2020 6/13/2020 RC 6/15/2020 4 6/16/2020 6/19/2020 TB 6/15/2020 1 6/16/2020 6/19/2020 RC 6/15/2020 3 6/17/2020 6/19/2020 TB 6/15/2020 2 6/17/2020 6/19/2020 FB 6/15/2020 3 6/18/2020 6/19/2020 RC 6/15/2020 4 6/18/2020 6/19/2020 TB 6/15/2020 2 6/18/2020 6/19/2020 FB 6/15/2020 5 6/19/2020 6/19/2020 RC 6/15/2020 1 6/19/2020 6/19/2020 TB 6/15/2020 1 6/19/2020 6/19/2020 FB 6/22/2020 3 6/22/2020 6/27/2020 RC 6/22/2020 5 6/22/2020 6/27/2020 TB 6/22/2020 1 6/22/2020 6/27/2020 FB 6/22/2020 2 6/23/2020 6/27/2020 RC 6/22/2020 2 6/23/2020 6/27/2020 TB 6/22/2020 4 6/23/2020 6/27/2020 FB 6/22/2020 3 6/24/2020 6/27/2020 RC 6/22/2020 1 6/24/2020 6/27/2020 TB 6/22/2020 5 6/24/2020 6/27/2020 RC 6/22/2020 3 6/25/2020 6/27/2020 TB 6/22/2020 11 6/25/2020 6/27/2020 FB 6/22/2020 8 6/26/2020 6/27/2020 RC 6/22/2020 4 6/26/2020 6/27/2020 TB 6/22/2020 2 6/26/2020 6/27/2020 FB 6/22/2020 2 6/27/2020 6/27/2020 TB 6/22/2020 4 6/27/2020 6/27/2020 Any assistance would be appreciated.Solved2.1KViews0likes4Comments