Creating a measure for Working Days with "Holiday Dates"
I have a Calendar that I've created in DAX and I want to create a column that shows "Is A Working Day" 0=Not a working day 1=Is A Working Day I want to create a variable calculation where I can include a "Conditional If statement" including Dates in the measure to return "0" for those dates that are holidays. Example: 5/30/2022 = 0 7/4/2022 = 0 This is how my calendar is laid out And this is my measure for "IsWorkingDay" I feel that I can turn this measure into variables where I can have both "IsWorkingDay" measure and the "IF statement" to create my column. I'm still very new to creating DAX measure but I'm slowly picking it up. The help would be greatly appreciated
LINEAR REGRESSION - X-AXIS IN TEXT FORMAT
Hello I am working with a data sample that contains numeric values on "y" axis (pH results) and text values on "x" axis (sample/batch code) and need to build a linear regression analysis. So far, I found a website "IterationInsights" that published an article "How to do simple linear regression in Power BI", which really helped me to analyse using date, but not sample code (since it is a text). I need to evaluate the trend between 2 samples, independent of when they were collected. Note that if x is a Date, we have: y = 0.0023x - 99.065, r² = 0.2566 But if x is the sample, we have: y = 0.0297x + 5.5252, r² = 0.3024 How am I able to build a variable table that converts the sample code to a numeric sequence (A1AA/22 = 1, A2AA/22 = 2, ...., A21AA/22 = 21)? Besides, it's possible to filter this table? Ex: analyse A12AA/22 to A20AA/22 (interval which would automatically be converted to the numbers: A12AA/22 = 1, A13AA/22 = 2, ...., A20AA/22 = 9, always starting with 1). Since slope formula is: [n(Σxy) - (Σx)(Σy)] / [n(Σx²) - (Σx)²], x should be refeered as a sequence of natural numbers representing the samples. n is the distinct count of the samples. I suppose that I should build a data var table to convert sample code to a number, and use All selected function to filter the samples I'm working with, but I'm really having a hard time to combine these functions and obtain a functional linear regression equation. I exemplified the solpe formula because I think the same solution would be applied to intercep, r², etc. For slope, I used the formula: --------------------------------------------------- SLOPE = var data = SELECTCOLUMNS(ALLSELECTED(DATA), "x_values",DATA[DATE], "y_values",DATA[RESULTS] ) --Variables to Solve for: var y_sum = SUMX(data,[y_values]) var x_sum = SUMX(data,[x_values]) var x2 = SUMX(data,[x_values]^2) var xy = SUMX(data,[x_values]*[y_values]) var row_count = COUNTROWS(data) --Solve for formula var Slope = ((row_count * xy) - (x_sum * y_sum))/((row_count*x2)-((x_sum)^2)) return Slope ----------------------------------------------------- Anyone could help me?
How to get revenue for the last date in month
Hi, I need to write a measure for revenue based on a changing max date. This is the problem: in my raw data, I get the date for each stage change, meaning that one order will have for example 3 milestones with a different date and revenue associated. I need to report the revenue for the latest change within a milestones and a month and ignore all other revenue information. Let's say I have only one order to consider. Left is what I get as raw data from my databank, right is what I need to report: Is there anyway to define the revenue per milestone and link the max date information with a slicer (=showing up the last day of each month for the report to automatically adapt)? Any help for this will be really appreciated! Thanks, Pauline.
How to Use a variable as filter in caclulate
I am trying to sum all the employees that left between 30 to 365 days. how can I use the "flag" in the calculate formula? # Under 1 year = var workStartDate=MAX('Employee data for PBI'[Work Start Date]) var workEndDate=MAX('Employee data for PBI'[Final Process Date]) var seniority= DATEDIFF(workStartDate, workEndDate, DAY) var flag=IF(seniority>=30 && seniority<=365,1,0) var emps=CALCULATE(SUM('Employee data for PBI'[# Terminated])) return empsIncrement Variable based off of category
Hi there, I have a scenario when I want to assign common variables to rows based on the values of a column within that row. Basically I have a number of Groups of students within a number of schools but the groups are always named differently, my report has a slicer on school but I want pages of my report to always point to seperate groups. So page 1 will always filter to an arbitrary 'Group 1' within the school the slicer is on. My current data structure in short is: School Group Student School A Rachel's Year 7's xxx School A Rachel's Year 7s xxx School A Sam Year 9s xxx School B Rachel's Year 8's xxx School B one to one sessions xxx Anything can be put into the group column, and I have hundreds of unique group names. I want a DAX calculated column that simply assigns Group 1 to a specific Group within a school and then Group 2 to the next and then starts again when reaching the next school. So something like this: School Group Student Arbitrary Group School A Rachel's Year 7's xxx Group 1 School A Rachel's Year 7s xxx Group 1 School A Sam Year 9s xxx Group 2 School B Rachel's Year 8's xxx Group 1 School B one to one sessions xxx Group 2 This would allow me to assign Group 1 as a page level filter in my report and that page will always show the first group in whichever school is selected, without having to manually filter it each time the school is changed. Any help would be great! Thanks,Variable Value - What if Parameter not showing/calculating as entered.
I have a view with a couple what if parameters includeds. The Service fees as shown is working just fine, but when I swtich to an entry view for the manual price I can't get it to react to exactly what I'm entering as the value. In this case I tried to enter 3.24 and as you can see with the criteria below that value would be on the created table. But the visual just jumps to what's shown here. Similar if I try an enter 7.25 for a moment it will show 7.99999999 and then when I click away it will push to 8.00. If there any other walk throughs I'm missing or views on what I'm doing wrong with this method of trying to enter a direct figure/value? Manual Price = GENERATESERIES(0.00 , 299.99 , 0.01 )Average Calculation
How would you write this query? It is to add up different columns * 200000 / sum of another column. I'm trying to break it down as simple as possible. StartDate is a date 12 months ago. It doesn't allow me to do calculations on var. Thanks in Advance. DART rolling average 2 = var _LT = Calculate(sum(HSE[Lost Time]), ,FILTER(HSE,HSE[Date] > Value([StartDate])), var _RR = Calculate(sum(HSE[Restricted Recordable]),FILTER(HSE,HSE[Date] > Value([StartDate])), var _OR = Calculate(sum(HSE[Other Recordable]),FILTER(HSE,HSE[Date] > Value([StartDate])), var _sums = calculate((_LT + [_OR] + [_RR] * 200000), var _hours = Calculate(sum(HSE[Hours]),FILTER(HSE,HSE[Date] > Value([StartDate]), return _sums/_hours
