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 ADMT
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.
Variable date table with parameter as enddate
Hi Im trying to make a calendartable with the enddate based on a parameter. These are the parameter values. My goal is to set the enddate based on today + the value selected by the slicer/parameter This is a part of the table where i set my start and enddate but unfortunatelly this way it doesnt work : DIM_Dates = ADDCOLUMNS ( CALENDAR (DATE(2020,1,1), Today()+Enddate[daysinfuture]), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] )) Help/ tips are welcome how to fix this.
Dateslicer controlled by a measure filter
Hi All Hope someone can help. This Is bugging the heck out of me. Examble images at the end of the post. Link to onedrive file - hope this works In order to enhance the UI I am trying to create a filter where from where the user can specify set intervals of the past 35 days, the past 60 days and the past 420 days (these are just exambles). When the filter is selected the date slicer on the page must be filtered accordingly. So if no filter is selected the entire period is available and if for instance 'offset -35' is selected the date slicer is limited to days tat are no less than 35 days ago as well. I figured I would add a binary filter to my date slicer – 1 or 0 based and a measure that produces 1 if the value is larger than the specified offset number i.e. -35 else 0. See Image1. Below I have a measure – DateFilter – based on a disconnected table (image2). It outputs either 1 or 0 relative to the offset value in the Dimension date table. This seemed to work when I tested in a table and when I set is up as a filter LIST. When I select -35 DateFilter returns 1 for the desired period and 0 before. See image3 and Image4. But when set as a slicer it doesn’t work. The slicer is can't be modified and just shows dd-mm-yyyy. See image5. I am guessing it’s because the slicer doesn’t have a selected value. Any Ideas as to how I solve this? A big Thank You in advance. Best regards Kasper Datefilter = VAR SelectDaysOffset = SELECTEDVALUE ( D_Periodfilter[DaysOffset] ) VAR Datefilter = SWITCH ( TRUE (), SELECTEDVALUE ( D_Datotable[OffsetDate] )> SelectDaysOffset, 1, 0 ) RETURN Datefilter Image1 Image2 Image3 Image4 Image5
Dynamic Min and Max Date Field Based on Values From Slicer Selection
I am trying to get the first score, and the last score based on the dates selected in slicer. I created a date table with DAX and joined to this table one to many, then created a measure to get the min and max date from ALLSELECTED (using between dates slicer). But when I try to use those fields as variables in a dax measure, I am getting errors such as only expecting one value. My end goal is to get the average of the first scores and last scores per wound to see if they have improved or not. This would be a great start, thanks -----------------------TABLE: If Slicer selection dates between 1/5/2020 and 1/23/2020 ---------------DAX MEASURE SO FAR: Last Visit Date = VAR patient = 'Query1'[PatientID] VAR wound = 'Query1'[WoundSite] VAR startdate = [Start Date Measure] VAR enddate = [End Date Measure] RETURN MAXX( FILTER( ALL('Query1'),'Query1'[PatientID] = patient && 'Query1'[WoundSite] = wound && Query1[VisitDate] <= enddate && Query1[VisitDate] >= startdate ),Query1[VisitDate])Date slicer - Max period multi select not updating Stacked Column chart
I have the following (simplified) stacked column chart which counts the number of issues per source: Based on this (simplified) data: I then apply a date slicer which allows multiple selections The date slicer is joined in the model and works correctly by adjusting the number of issues if one month is selected at a time. When multiple months are selected then it need only evaluate the most recent month which I have calculated with the following 2 measures: MaxPeriod = CALCULATE ( MAX ( vw_DimDate[Month] )) IsMaxPeriod = IF(NOT(ISBLANK([MaxPeriod])), IF( MAX ( vw_PBI_MR_IssuesByAct[Period] ) = [MaxPeriod], 1, 0 )) Confirmed to be working with a table visual: If I then apply this filter, the table visual shows the correct count of 39 for the period of 202006 The problem is that the stacked bar chart does not-it shows 70 issues rather than 39 for 202006-any ideas on how to solve this?
