Calculate time between 8 - 5PM
I am running to an issue that I can seem to calculate the time correctly. My goal is to show all the hours between 8AM to 5PM (working hours). I have a log in time and a log out time. I would like to return the out but showing only the working hours period, I have figure out for the morning, if login and logout time is before 8AM it will be BLANK. And if login time is before 8AM but logout time 8:10, that’s mean they have been working for 10 minutes. I have fixed the issue but for hours after 5PM, I couldn’t get my data to come out right. Example: Hudson Login Time is 4:41:43 and Logout at 6:03:36 PM. The Total Available Time is 1:21:53 for that day. Total Available Time between 8 – 5PM should be 00:18:17 and not 23:41:43. Code: Available Time = CALCULATE( SUMX( FILTER('BDA', 'BDA'[Status] = "Available"), 'BDA'[Logout Time] - 'BDA'[Login Time] ) ) Available Time (8AM to 5PM) = VAR TotalAvailableTime = SUMX( FILTER('BDA', 'BDA'[Status] = "Available"), IF( HOUR('BDA'[LogIn Time]) < 8 && HOUR('BDA'[Logout Time]) < 8, BLANK(), -- Leave blank if both times are before 8 AM IF( HOUR('BDA'[LogIn Time]) >= 17 && HOUR('BDA'[Logout Time]) >= 17, BLANK(), -- Leave blank if both times are after 5 PM IF( HOUR('BDA'[LogIn Time]) < 8, -- Calculate if LogIn Time is before 8 AM IF( 'BDA'[Logout Time] >= TIME(8, 0, 0), 'BDA'[Logout Time] - TIME(8, 0, 0), BLANK() ), IF( HOUR('BDA'[Logout Time]) > 17, TIME(17, 0, 0) - 'BDA'[LogIn Time], IF( HOUR('BDA'[LogIn Time]) >= 8 && HOUR('BDA'[Logout Time]) <= 17, -- Calculate the time between 8 AM and 5 PM 'BDA'[Logout Time] - 'BDA'[LogIn Time], BLANK() ) ) ) ) ) ) RETURN FORMAT(TotalAvailableTime, "hh:mm:ss")Get value from recent quarter based on date and month column.
Hi ... I have below table (sample data). Date Year Month Purchase Loan Rental 31/3/2020 2020 3 6,154.57 1,005.50 867.71 30/6/2020 2020 6 1,144.23 520.21 630.16 30/9/2020 2020 9 211.06 34.68 328.65 31/12/2020 2020 12 520.21 540.28 848.30 31/12/2020 2020 13 1,144.23 520.21 630.16 31/3/2021 2021 3 937.13 551.11 322.45 30/6/2021 2021 6 1,093.46 211.06 78.53 30/9/2021 2021 9 621.56 118.47 231.10 31/12/2021 2021 12 46.20 937.13 1,337.47 31/12/2021 2021 13 937.13 551.11 322.45 31/3/2022 2022 3 497.50 222.12 46.20 30/6/2022 2022 6 11,947.51 4.17 322.45 30/9/2022 2022 9 1,434.00 893.86 621.56 31/12/2022 2022 12 879.00 4,620.00 497.50 31/3/2023 2023 3 937.13 540.28 322.45 30/6/2023 2023 6 211.06 937.13 848.30 For month december, I will have two record. Month 12 is a first record and Month 13 is a final record. If month 13 not exist, dax should pickup data from month 12. From above statement, how can I creata a view/working table from above master data as sample below. View/Working Table #1: Date Description Amount 31/3/2020 Purchase 6,154.57 31/3/2020 Loan 1,005.50 31/3/2020 Rental 867.71 30/6/2020 Purchase 1,144.23 30/6/2020 Loan 520.21 30/6/2020 Rental 630.16 30/9/2020 Purchase 211.06 30/9/2020 Loan 34.68 30/9/2020 Rental 328.65 31/12/2020 Purchase 1,144.23 31/12/2020 Loan 520.21 31/12/2020 Rental 630.16 View/Working Table #2: Date Purchase Loan Rental 31/3/2020 6,154.57 1,005.50 867.71 30/6/2020 1,144.23 520.21 630.16 30/9/2020 211.06 34.68 328.65 31/12/2020 1,144.23 520.21 630.16 After that, I want to create a measure to display data in my report. Example, if current date is 31/08/2020, the result should display quarter 2 value based on category. I hope my explanation is clear... 🙂 Regards, NickzNickzdisplaying data by category and latest date
Hi, I have the below table (new version) for my testing data. How can I create a measure for a line chart to display the latest amount based on the category, date and month? The reason why I need to filter by date and month is because there will be a month 13. Month 13 is an Audited Amount. From there, I will filter the data to the latest 3 years. Below is the measure that I used. If anyone has the simplified version, please do advise me. Last 3 Years = VAR _currentyear = YEAR(TODAY()) VAR _previousyear = SELECTEDVALUE(test4_3years[Year]) RETURN SWITCH( TRUE(), _previousyear <= _currentyear -1 && _previousyear >= _currentyear -3, 1, 0 ) Thank you. Regards, NickzNickzOnly display last 3years record with year end condition...
Hi ... I need to make a line chart with trend lines. Below is a record (sample data) with the accumulated amount... For December, there will be a month 13 (audited account). How can I only display only last 3 recent years (full year)... If the date (December) has month 13, the display should pick up that month instead of month 12... Below is the measure that I have currently to show the data for KPI Card: Receivables (RM) = VAR _Result = CALCULATE( SUM( fin_table[Receivables (M)] ), fin_table[Date] = MAX( fin_table[Date] ) && ( fin_table[Month] ) ) RETURN IF(ISBLANK(_Result),"-",_Result) Your help is highly appreciated.... Thank you. Regards, NickzNickz
Find the nearest value within a month
Hello community! Is anyone familiar with writing the Dax Programming to find the nearest (closest ) value in the table? For example, My table is like this: And I want to find the nearest Model built time value with 1 month average built time (in this case, today date is 2022/11/16 so (213 + 444 + 532 + 111 / 4 ) = 325 ) so the nearest value of 325 in this table should be 213 (A125) not 345 (A124) as that model not in a past month from current date. Therefore, how do i return 213 using the dax programming? Thank you so much for your help!
