How to calculate average of worktime from slicer date
Hi everyone. My problem is the present working time of an employee in the company. In the report, it has a date slicer, a count of working days, and the working hour of the employee in duration time. Currently, I want to present average of working day and average working hour in duration time from date slicer. My data below Table 'working_time' has columns: emloyee_id, workingdate, worktime_hrs (worktime hour in day), worktime_day (worktime day has default is 1) Depend on table sum worktime group by employee, the sum worktime is the total value divided by 5 employees. My expectation is the average of worktime_day should be (3+3+2+2+3) / 5 = 2.6 and the average of worktime_hrs should be (24+24+16+16+24) / 5 = 20.8. But I don't know how to calculate this average value. I hope everyone will spare their precious time to help me solve this problem. Thanks you so much!
Context issue when displaying prior year total that is based on date and category
My goal is to create a table that shows total applicants by action status for both selected and prior periods using the status that they had as of a selected date AND admission term. In other words, I want to count the students under the status for the row that is the the max effective dated row that is less than or equal to the selected date. I was able to do it for the selected values, but I'm having trouble showing the prior year in the same visual. My data set is a list of student applications and their statuses (sample below). Each student has multiple rows of data that are effective dated but also have a sequence number in case 2 transactions happen on the same day. What works: I have a measure that correctly calculates the number of applicants in each status as of the Admit Term and Date selected. I have also created a second measure based on this to calculate the prior year by finding that from the selected values (below). This measure appears to return the correct value when I EVALUATE it in DAX Query. If I return the variables as the output for the measure, they select the correct values. For example, if I select Fall 2024 and return _lastTerm, the measure returns Fall 2023. Not Working: When I place both these measures in a visual together, the prior year shows up blank. I’m sure that this is a context issue, but can’t figure out how to fix this. Any help will be appreciated. Desired output is to show the corresponding counts for selected and prior Term/Date combination. Measure (note: current year uses same measure but without the "last" variables) Applicant Status Prior Year = var _selectedDate = DATEVALUE(MAX('DimDate'[CalendarDate])) //get slicer value for effective date var _selectedTerm = SELECTEDVALUE('Application data'[AdmitTermCode]) //get slicer value for Admit Term var _selectedTermSeason = SELECTEDVALUE('Application data'[TermSeason]) //get associated Term Season var _lastyear = //calculate same day last year CALCULATE( MAX('DimDate'[CalendarDate]), SAMEPERIODLASTYEAR('DimDate'[CalendarDate])) var _lastTerm = CALCULATE(MAX('DimTerm'[TermCode]) //get term code from last year , ALL('DimTerm') , 'DimTerm'[TermCode] < _selectedTerm , 'DimTerm'[TermSeason] = _selectedTermSeason) --filter app data to only row for selected term and before selected date var _onlyBeforeDate = SUMMARIZE( FILTER(ALL('Application data') , 'Application data'[AdmitTermCode] = _lastTerm && 'Application data'[EffectiveDate] <= _lastyear) , 'Application data'[StudentNumber] , 'Application data'[z_PartitionKey] , 'Application data'[AppSequence]) -- Determine the most recent row before the selected date for each application var _maxEffectiveDate = INDEX(1 , _onlyBeforeDate , ORDERBY('Application data'[AppSequence], DESC) , PARTITIONBY('Application data'[z_PartitionKey])) return CALCULATE(DISTINCTCOUNT('Application data'[StudentNumber]), _maxEffectiveDate) Model: Example of Data: Note that multiple admit terms are accepting applications on any given calendar date, so date can't be the only value used to define the current/prior periods. StudentNumber ApplicationNumber AdmitTermCode TermSeason AppSequence z_PartitionKey EffectiveDate ProgramActionCode 123456 560495 1241 Fall 1 123456_560495 2024-05-27 0:00 APPL 123456 560495 1241 Fall 2 123456_560495 2024-05-28 0:00 ADMT 123456 560495 1241 Fall 3 123456_560495 2024-06-03 0:00 MATR 123457 558997 1241 Fall 1 123457_558997 2024-05-04 0:00 APPL 123457 558997 1241 Fall 2 123457_558997 2024-05-05 0:00 ADMT 123457 558997 1241 Fall 3 123457_558997 2024-05-17 0:00 MATR 123456 504500 1231 Fall 1 123463_504500 2022-10-31 0:00 APPL 123456 504500 1231 Fall 2 123463_504500 2023-02-01 0:00 APPL 123456 504500 1231 Fall 3 123463_504500 2023-02-01 0:00 ADMTDate Slicer - Latest Date
When using a date slicer it would be great if it existed an option "earliest date" (or latest). The issue is that when you have a table that is constantly adding new records, with a date column, the default status of the slicer gets outdated. Example: the default status of the slicer, and where the bookmark was saved is on period 2019.05. When a new record 2019.06 is added to the table, the bookmark needs to be saved and bookmark updated, this every month. I know there are DAX workarounds, I am using them, but an "latest date" option on the date filter would save us this extra work.
Get the latest record in an SCD2 for a unique ID, count unique IDS, within period set by date slicer
Hi all I have been struggling with this challenge latetly. My goal is to create a measure that count unique IDs that has the "Priority" of 1, within a set date period from the date slicer. The data comes from an SCD2 table that records entries of all unique contracts ("Applicants"). The one column that changes frequently is the column named "Priority". My goal is to, within the set date period from the date slicer, find the latest entry for all unique contracts and then do a unique count for all with "Priority" of 1. "Priority" can be filtered on the report page as well so I guess that filtering doesnt need to be done in DAX. The measure uses USERELATIONSHIP with "ApplicationDate". In plains SQL this can be solved with a ROW_NUM() with descending order, and then filter on the row number. I havent managed to use ROWNUMBER() in DAX in a dynamic way, having the date slicer dictating the ROWNUMBER() output and passing it forward in the measure. One way I could think of solving this is to lock in the count for each locked time period (say per week), but the users would like to see if it works with the date slicer. Sample from my table (Sorted by RecordEffectiveDate): MemberIDSource Priority ApplicationDate RecordEffectiveDate IsCurrent 454 1 2023-01-07T16:36:04.000+00:00 2023-01-10T00:00:00.000+00:00 FALSE 454 2 2023-01-07T16:36:04.000+00:00 2023-01-21T00:00:00.000+00:00 FALSE 789 1 2023-01-19T17:16:05.000+00:00 2023-01-21T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-08T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-08T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-09T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-09T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-10T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-16T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-07-19T00:00:00.000+00:00 FALSE 789 3 2023-01-19T17:16:05.000+00:00 2023-07-24T00:00:00.000+00:00 TRUE 1011 1 2023-07-20T13:17:14.000+00:00 2023-07-24T00:00:00.000+00:00 FALSE 454 2 2023-01-07T16:36:04.000+00:00 2023-07-24T00:00:00.000+00:00 FALSE 1011 2 2023-07-20T13:17:14.000+00:00 2023-08-02T00:00:00.000+00:00 TRUE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-02T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-23T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-24T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-24T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-25T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-09-22T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-12-06T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2024-01-13T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2024-01-31T00:00:00.000+00:00 TRUE Any help very much appreciated.
A Between option for the Relative Date Filter
Hello Everyone, A common issue i often come accross when building reports is finding the best way to show date filtering and also keep the dates dynamic. The Relative Date option for the Slicer is an awesome tool, but it sadly has a major drawback. My customers are not able to both view back and forward in time at the same time. This is especially an issue when dealing with forecasts, since holding a forcast up to historic values is very important. What i suggest is to add a "Between" or "Combine" option under the "Last", "Next" and "This" options. So the customer can choose to see 4 months back and 3 months ahead for instance, since today this needs to be hardcoded into a calendar table, if the date also needs to move dynamically. I believe that this change would please a lot og users and make the "Relative Date" option much more useable. Thanks in advance
