Masure of The difference between individual months for 12 RM
Hello, I'd like to insert a column with calculated difference between the previous months and 'current' months. The difference should base on the values from 12 RM measure as indicated below. 12 RM Category rate = IF(AND(ISFILTERED('Calendar'[Month Yr]), DATEDIFF(MAX('Calendar'[Dates]), TODAY()-1,MONTH)<>0), SUMX(VALUES('Calendar'[Month Yr]), [Pre 12 RM Cat rate]) , --To force the '0' and the division by 12 for the RollBack [Pre 12 RM Cat rate]) Pre 12 RM Cat rate = VAR RollBack_Table = CALCULATETABLE(FILTER(DATESINPERIOD('Calendar'[Dates], MAX('Calendar'[Dates]), - 12 , MONTH) , 'Calendar'[Dates] = EOMONTH('Calendar'[Dates],0)) , 'Calendar'[Flag_Projec]) VAR RollBack_Table_2 = CALCULATETABLE(FILTER(DATESINPERIOD('Calendar'[Dates], MAX('Calendar'[Dates]), - 11 , MONTH) , 'Calendar'[Dates] = EOMONTH('Calendar'[Dates],0)) , 'Calendar'[Flag_Projec]) VAR Cat_current = CALCULATE([MTD cat rate], 'Calendar'[Current Month]) RETURN IF(AND(ISFILTERED('Calendar'[Month Yr]), DATEDIFF(MAX('Calendar'[Dates]), TODAY()-1,MONTH)<>0), SUMX(RollBack_Table , [MTD cat rate]) , SUMX(RollBack_Table_2,[MTD cat rate]) + Cat_current ) Do you have any ideas how could I cope with that? Thank you in advance! MikSubtract if statement from a measure (working days)
Is there a way to replace 2.5 in Measure 5.0 with this IF statement [Rules]? Measure 5.0 = VAR _Month = CALCULATE(COUNT(DimDate[Days in Month]),FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK())) VAR _lastday= COUNTROWS( FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))-2.5 RETURN IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday ) Rules = VAR searcind = SEARCH("Industr",'Query1'[GRADE_NAME],1,BLANK()) VAR searcnotind = FIND("Industr",'Query1'[GRADE_NAME],1,-1) VAR duration510 = IF(searcind<>BLANK(),IF(AND('Query1'[Total Years - Employment] >=0,'Query1'[Total Years - Employment] <5),CALCULATE(23.5/12),BLANK()),BLANK()) VAR notindnot10 = IF(AND('Query1'[Total Years - Employment] <10,searcnotind),CALCULATE(25/12)) VAR ind5 = IF(AND('Query1'[Total Years - Employment] >= 5,searcind),CALCULATE(25.5/12),duration510) VAR notind10 = IF(AND('Query1'[Total Years - Employment] >= 10,searcnotind),CALCULATE(30/12),notindnot10) RETURN IF(ind5<>BLANK(),ind5,notind10) https://www.dropbox.com/s/xjagu2e89p5fe4a/Sample4.pbix?dl=0Subtract Measure Values from Column
Is there a way to subtract column values [Rules] from a measure? My measure which gives me number of working days in a month is: Measure 5 = VAR _Month = CALCULATE(COUNT(DimDate[Days in Month]),FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK())) VAR _lastday= COUNTROWS( FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK())) RETURN IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday ) Thanks, https://www.dropbox.com/s/vojq25pkazt4lmj/Sample5.pbix?dl=0Insert a limit in a measure to avoid below 0 or above 100
I have a 3 measures related to absence: 1 Calculate number of working days in month - (2.5 days leave allowed) Measure 5.1 = VAR _Month = CALCULATE(COUNT(DimDate[Days in Month]),FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK())) VAR _lastday= COUNTROWS( FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))-2.5 RETURN IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday ) 2. Number of absence days from dates : [AbsentbyDay] 3. Measure 1 and Measure 2 subtracted Measure 5.5 = VAR _Month = CALCULATE(COUNT(DimDate[Days in Month]),FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK())) VAR _lastday= COUNTROWS( FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))-2.5 RETURN IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday )-[AbsentByDay] My question is : is there a way to limit the NET values in table 3 from going below 0 / over 100 Thanks in advance, apologies if I haven't explained this properly. File attached to help https://www.dropbox.com/s/u5ztxrg4g2g5qpi/Sample3%28updated%29.pbix?dl=0Subtracting Calculated Columns from Each Other
I have absence data: ID Start End Duration Days in Month Working Days Leave Entitlements Days Available Absence Net 727 02/01/2019 07/01/2019 6 31 23 2.5 20.5 6 14.5 727 10/03/2020 11/03/2020 2 31 22 2.5 19.5 2 17.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 7 -48.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 31 -48.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 28 -48.5 Where I'm subtracting [END] - [START] = Duration and then subtracting the ABSENCE from this. However for periods longer than 1 month it groups the total (66 days) rather than individual days (7,31,28) is there a way to get it so the chart is like: ID Start End Duration Days in Month Working Days Leave Entitlements Days Available Absence Net 727 02/01/2019 07/01/2019 6 31 23 2.5 20.5 6 14.5 727 10/03/2020 11/03/2020 2 31 22 2.5 19.5 2 17.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 7 10.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 31 -13.5 1150 03/06/2019 07/08/2019 66 30 20 2.5 17.5 28 -10.5 PBIX attached for better explanation . https://www.dropbox.com/s/zjizlyun1nuur15/Sample%20Absence.pbix?dl=0Can I subtract one filtered value from another?
Every month I import our department spend into Power BI to compare our budget vs actual spend. Obviously I have the budget for the full year, but only have spending data available to-date for actual spend. Each month I filter total budget spend by the YTD months (i.e. at the moment excluding May and June), so that it lines up with our actual spend. I.e - Budget YTD: $110,000 (May-19 to Apr-20) Actual spend YTD: $117,000 Is there a way to subtract the YTD Spend from the filtered YTD Budget (i.e. = -$7,000)? Obviously the filter changes each month. Not sure if relevant, but our Actuals and Budget are in different sheets with a 1 to many connection. Any questions let me know as I already typed this out once before getting signed out and having to start again!
