How to subtract one more month to DATEDIFF result (only if total is 0 or greater than 0)?
I have following measure: ForecastMonths = DATEDIFF( FIRSTDATE ('Table'[Survey Date]), LASTDATE ('Calendar'[Month Year]), MONTH ) I am trying to adjust the result (which comes in number of month) to one more/less month. I would like the result to be either 91 or 89. How do I go about doing it? Thanks for help.
DAX calculated measure only working in a specific row before any FILTER is applied
Hi, I have been unable to figure out the following issue: I cannot calculate measures in my total (calculated) rows (i.e Total = Measure 1 + Measure 2) of my Expense statement Layout template because Measure 1 (or Measure 2) is only calculated in it's own row and the Total row stays blanks (See Screenshot#1). The measure calculations do not work when I use my RM Expense Layout table (using formula #1 or #2) The measure calculations work if I use the (sub)header column (Group Level 2 (CALC) from my CC Mapping Table (equivalent to a Chart of Accounts) with Formula #1 My question is: How can I make the result in screenshot #2 work with my RM Expense Layout table? Why are the measures only working as inteded with the [GROUP Level 2 (CALC)] column table layout? Below, shows the relationships between my tables Formula #1: NFRM = CALCULATE( SUM('06. Hyperion Data (GL)'[Amount]), '03. CC Mapping'[Group Level 2 (CALC)]="Non-Financial Risk." ) Formula #2: SUM('06. Hyperion Data (GL)'[Amount]), '05. RM Expense Layout'[RM Expense Layout]="Non-Financial Risk." ) Screenshot #1: The calculated the NFRM measure only works for the header that matches the text in Group Level 2 (CALC) column (CC Mapping table) or the text from the RM Exp Layout column in the RM Expense Layout table. Screenshot #2: Measure works in all the rows before any filter is applied. With formula #2, calculation only happens in Non-Financial Risk row.
Need help on Sequence number in same column and Horizontal records in same row
Hi All, Please find the below screenshot of sample data, need help on the "Horizontal records" in a same row and "Sequence number" for different Insureds numbers. Sample Data: Below are the requirements need to work on the DAX calculated column: "Upload ID", "Policyholder Name" and "InsuredS". Please help. Requirement: 1. Under "UploadId" and "Policyholder Name" In case multiple values are present for a single Application Number, list all items need to display horizontally, separated by a space. Maximum of 20 characters allowed, in case this limit will be exceeded display "..." at the end 2. For "Insured Branch Number" new calculated column display a sequence of numbers starting from "1" in case multiple insureds are present within the policy Expected OUTPUT: Here is the OUTPUT need to display in Power BI with the help of sample data. Please help. Sample Data Reocrds: Here is the sample records can work for this expected OUTPUT. Policy Product Type Application Number Image Upload Date and Time Upload ID Policyholder Client ID Policyholder Name InsuredS 9000123 GH 30330330311 2024/7/03 13:00:02 bheemvi 7185244885 bheemvi 8541224151 1236121 RT 77879174306 2024/7/03 13:00:02 surfati 5535470245 surfati 7563424173 3954848 MM 40760081519 2024/7/03 13:29:30 kudou 7128024728 kudou 5535470245 3954848 MM 40760081519 2024/7/03 13:29:30 raju 7128024728 raju 9641540351 3954848 MM 40760081519 2024/7/03 13:29:30 venky 7128024728 venky 8541224151 6864730 CN 40510010297 2024/7/03 14:02:99 suresh 8541224151 suresh 7563424173 9000520 BO 40760081311 2024/7/03 14:02:99 naresh 7563424173 naresh 5535470245 1236121 RT 77879174306 2024/7/03 15:00:02 gopal 5535470245 surfati 9873424173Find Last Value by MAX date and SUM
I have an inventory audit table. I created a measure to find the qty of the last record date for each warehouse and bin, however the grand total is showing 0 but I want it to SUM that measure. For the example below, I want the grand total to show 37. Qty Last Value = CALCULATE(SELECTEDVALUE('Inventory Audit By Date'[Qty On Hand]), 'Inventory Audit By Date'[Date] = MAX('Inventory Audit By Date'[Date])) *Date Max is also a measure Date MAX = MAX('Inventory Audit By Date'[Date])
Count person who are joining and leaving
Hi. Seeking for your help regarding DAX. I want to create a DAX that counts the people who join and leave the online event. I have created a visual that counts who joins in a specific hour. However, I wish to make a visual that adds the people to the count who join the online event and subtracts the number of people who leave in an hourly interval. Below is the sample data Name start_time end_time duration (seconds) Duration (Minutes) Duration (Hour) PERSON_1131 12/03/2024 6:48:55 am 12/03/2024 6:48:56 am 1 0 0 PERSON_1282 12/03/2024 6:49:10 am 12/03/2024 6:49:19 am 9 0.2 0 PERSON_1320 12/03/2024 6:49:26 am 12/03/2024 6:49:34 am 8 0.1 0 PERSON_1326 12/03/2024 6:50:57 am 12/03/2024 6:51:06 am 49 0.8 0 PERSON_1107 12/03/2024 6:53:47 am 12/03/2024 6:53:53 am 6 0.1 0 PERSON_1076 12/03/2024 6:58:49 am 12/03/2024 6:58:51 am 2 0 0 PERSON_1004 12/03/2024 6:49:12 am 12/03/2024 6:59:40 am 1028 17.1 0.3 PERSON_1217 12/03/2024 7:00:39 am 12/03/2024 7:00:44 am 5 0.1 0 PERSON_1298 12/03/2024 7:00:24 am 12/03/2024 7:00:44 am 20 0.3 0 PERSON_1217 12/03/2024 7:00:56 am 12/03/2024 7:00:59 am 3 0 0 PERSON_1426 12/03/2024 6:50:25 am 12/03/2024 7:01:00 am 5075 84.6 1.4 PERSON_1326 12/03/2024 6:51:14 am 12/03/2024 7:01:07 am 4993 83.2 1.4 PERSON_1081 12/03/2024 7:01:20 am 12/03/2024 7:01:23 am 3 0 0 PERSON_1426 12/03/2024 7:01:21 am 12/03/2024 7:01:24 am 3 0 0 PERSON_1326 12/03/2024 7:01:12 am 12/03/2024 7:01:57 am 45 0.8 0 PERSON_1130 12/03/2024 7:02:05 am 12/03/2024 7:02:08 am 3 0 0 PERSON_1006 12/03/2024 7:02:13 am 12/03/2024 7:02:14 am 1 0 0 PERSON_1068 12/03/2024 7:00:54 am 12/03/2024 7:02:18 am 164 2.7 0 PERSON_1231 12/03/2024 7:02:32 am 12/03/2024 7:02:35 am 3 0 0 PERSON_1076 12/03/2024 7:02:42 am 12/03/2024 7:02:44 am 2 0 0 PERSON_1060 12/03/2024 7:02:50 am 12/03/2024 7:02:51 am 1 0 0 PERSON_1147 12/03/2024 7:02:19 am 12/03/2024 7:02:55 am 36 0.6 0 PERSON_1210 12/03/2024 7:03:19 am 12/03/2024 7:03:20 am 1 0 0 PERSON_1564 12/03/2024 7:02:51 am 12/03/2024 7:03:26 am 75 1.2 0 PERSON_1581 12/03/2024 7:01:39 am 12/03/2024 7:03:36 am 197 3.3 0.1 PERSON_1051 12/03/2024 7:03:34 am 12/03/2024 7:03:48 am 14 0.2 0 PERSON_1074 12/03/2024 7:04:29 am 12/03/2024 7:04:31 am 2 0 0
DAX calculate Previous month lost customer revenue
Hi guys, I am having trouble calculating the previous month lost customer revenue. My model relies on a single way relationship between thr fact table and the time table, so that is not possible to change. I would like to display the measure "Churn_lost_$" as shown in the picture below. Thank you very much! 🤙 You can donload the model here: https://drive.google.com/file/d/1MKFmtBIm1Z-yJ0--FhVRuIrZqWVV166c/view?usp=sharing
Calculate percentage of employees not exceeding 5 consecutive days per week, with measures only
I am hitting a wall trying to create a measure that calculates the number of employees not exceeding 5 days worked consecutively per week (defined as Monday - Sunday, not a 7 day period). So far I have only been able to achieve it by first creating a calculated table with a summary of dates, employee id's, year_week and year_month, then some additional calculated columns and finally two measures. The issue with this is that since I am pulling the employee id's from the employee table and the dates, year_month and year_week from the date table, I cannot visualize it on a table visual that uses any date field (in my case I use year_month), or use any of my dimension table slicers related in any way to th date or employee table, and I cannot create a relationship back to the tables as it creates circular references. My initial table: FiveDaysWorkedInArowPerWeek = SUMMARIZECOLUMNS( 'm d_employees'[employee_id], 'm d_date'[date_date], 'm d_date'[year_week], 'm d_date'[year_month], FILTER( 'm d_employees', [Scheduled Hours from daily table] > 0 ), "scheduled hours", [Scheduled Hours from daily table] ) Then I have these calculated columns: Index = VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id] VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week] RETURN RANKX( FILTER( ALL(FiveDaysWorkedInArowPerWeek), FiveDaysWorkedInArowPerWeek[employee_id] = CurrentEMPLOYEEID && FiveDaysWorkedInArowPerWeek[year_week] = CurrentYearWeek ), FiveDaysWorkedInArowPerWeek[date_date], , ASC, Dense ) This just creates a basic index of all rows per employee and week. I.e. If the first day of the week is monday, they it gets 1, and if the last day is friday but wednesday was off, friday gets 4 as there were only 4 entries for that week. Consecutive Days Initial = VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id] VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week] VAR CurrentDate = FiveDaysWorkedInArowPerWeek[date_date] VAR CurrentIndex = FiveDaysWorkedInArowPerWeek[Index] VAR PreviousDate = CALCULATE( MAX(FiveDaysWorkedInArowPerWeek[date_date]), FILTER( ALLEXCEPT(FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[employee_id], FiveDaysWorkedInArowPerWeek[year_week]), FiveDaysWorkedInArowPerWeek[date_date] < CurrentDate && FiveDaysWorkedInArowPerWeek[Index] = CurrentIndex - 1 ) ) VAR DaysDifference = DATEDIFF(PreviousDate, CurrentDate, DAY) RETURN IF( CurrentIndex = 1 || DaysDifference > 1, 1, 0 ) Here the output gives a 1 if the current index is 1, i.e. the first day worked in a week / the first entry in a week, for an employee, and then it gives a 1 if the date is bigger than the previous date by more than 1 day. I.e: employee_id year_week date_date Index Consecutive days initital 1238574 202323 2023-06-05 (mon) 1 1 1238574 202323 2023-06-06 (tue) 2 0 1238574 202323 2023-06-07 (wed) 3 0 1238574 202323 2023-06-10 (sat) 4 1 1238574 202323 2023-06-11 (sun) 5 0 1238574 202324 2023-06-12 (mon) 1 1 1238574 202324 2023-06-13 (tue) 2 0 1238574 202324 2023-06-14 (wed) 3 0 1238574 202324 2023-06-16 (fri) 4 1 1238574 202324 2023-06-18 (sun) 5 1 1238574 202325 2023-06-24 (sat) 1 1 1238574 202326 2023-06-26 (mon) 1 1 1238574 202326 2023-06-27 (tue) 2 0 1238574 202326 2023-06-28 (wed) 3 0 1238574 202326 2023-06-29 (thu) 4 0 Consecutive Days = VAR CurrentEMPLOYEEID = FiveDaysWorkedInArowPerWeek[employee_id] VAR CurrentYearWeek = FiveDaysWorkedInArowPerWeek[year_week] VAR CurrentIndex = FiveDaysWorkedInArowPerWeek[Index] VAR ConsecutiveStart = CALCULATE( MAX(FiveDaysWorkedInArowPerWeek[Index]), FILTER( FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[employee_id] = CurrentEMPLOYEEID && FiveDaysWorkedInArowPerWeek[year_week] = CurrentYearWeek && FiveDaysWorkedInArowPerWeek[Index] <= CurrentIndex && FiveDaysWorkedInArowPerWeek[Consecutive Days Initial] = 1 ) ) RETURN CurrentIndex - ConsecutiveStart + 1 MaxConsecutiveDaysPerWeek = CALCULATE( MAX(FiveDaysWorkedInArowPerWeek[Consecutive Days]), ALLEXCEPT(FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[employee_id], FiveDaysWorkedInArowPerWeek[year_week]) ) Finally, here I sort the correct/intended indexing, i.e. it counts from 1 and up within any given week for an employee until it is a new week OR the previous day within a week for an employee is greater than one day apart. I.e. employee_id year_week date_date Index Consecutive days initital Consecutive days 1238574 202323 2023-06-05 (mon) 1 1 1 1238574 202323 2023-06-06 (tue) 2 0 2 1238574 202323 2023-06-07 (wed) 3 0 3 1238574 202323 2023-06-10 (sat) 4 1 1 1238574 202323 2023-06-11 (sun) 5 0 2 1238574 202324 2023-06-12 (mon) 1 1 1 1238574 202324 2023-06-13 (tue) 2 0 2 1238574 202324 2023-06-14 (wed) 3 0 3 1238574 202324 2023-06-16 (fri) 4 1 1 1238574 202324 2023-06-18 (sun) 5 1 1 1238574 202325 2023-06-24 (sat) 1 1 1 1238574 202326 2023-06-26 (mon) 1 1 1 1238574 202326 2023-06-27 (tue) 2 0 2 1238574 202326 2023-06-28 (wed) 3 0 3 1238574 202326 2023-06-29 (thu) 4 0 4 Now that I have the Consecutive Days column, I can go ahead and calculate the percentage of employees (that has any worktime), for each week, that does not exceed 5 (notice I just flip the percentage by subtracting from 1 in the return statement): PercentageFiveDaysPerWeek = VAR EmployeesFiveDaysOnly = CALCULATE( DISTINCTCOUNT(FiveDaysWorkedInArowPerWeek[employee_id]), FiveDaysWorkedInArowPerWeek[MaxConsecutiveDaysPerWeek] > 5 ) VAR TotalEmployees = [TotalEmployeesPerWeek] RETURN CALCULATE(1-DIVIDE(EmployeesFiveDaysOnly, TotalEmployees, 0)) And then I get to my final measure, where I calculate how many employees do not exceed 5 consecutive days per week, each month (as I am presenting per month): PercentageFiveDaysPerMonth = AVERAGEX( SUMMARIZE( FiveDaysWorkedInArowPerWeek, FiveDaysWorkedInArowPerWeek[year_month], "Percentage", [PercentageFiveDaysPerWeek] ), [Percentage] ) Again, this works fine on its own if I only want to output it in the table using the year_month column from the calculated table called 'FiveDaysWorkedInArowPerWeek'. However, since there are no relationships and they cannot be created due to circular dependencies, I cannot add slicers of any other dimension table on the page, or use the proper year_month column (the one from my 'm d_date' table). So users will not be able to filter on the location dimension connected to the employees table, not on the contract type of the employee, not on the account they belong to, not on any other attributes like WAH/Brick and Mortar, if they are temporary or permanent employees, etc. etc. I have other measures that work fine, but in this case I could not wrap my head around the complexity of this in order to make it into a series of measures that work dynamically with my dimension tables (i.e. the original employee table and the original date table). Note that the [Scheduled Hours from daily table] measure in the initial table calculation works dynamically wherever it lives as it just sums up time from my schedule table, which has relationships to both the employee and date table. Anyone have any suggestions?
