general comment

46 Topics

Accrued Vacation formula in power BI
Hi, I am working on tracking PTOs in power Bi. I want to create a DAX query on the basis of below parameters 1.) 0 – 2 years = 10 days per year 2 – 10 years = 15 days per year 10+ = 20 days per year 2) If anyone joins on or before 15th of a particular month, then he/she gets leaves for that particular month else from next month onwards. 3) Also, if any employee hits milestone in the current year, e.g. if one of the employee who joined on 9-aug-2022 is completing 2 yr milestone oln 9-aug-2024, ideally his accrual should be =((10/12)*7) + ((15/12)*4)=10.83 . I have tried using below formula but somehow it is not giving me desired output. Let me know if anyone has any inputs or suggestions... Accrued Vacation = VAR StartDate = SELECTEDVALUE('Employee Roster'[Start Date]) VAR CurrentDate = TODAY() VAR CurrentYear = YEAR(CurrentDate) VAR StartYear = YEAR(StartDate) VAR StartMonth = MONTH(StartDate) VAR CurrentMonth = MONTH(CurrentDate) VAR StartDay = DAY(StartDate) VAR Tenure = DATEDIFF(StartDate, CurrentDate, YEAR) VAR AnniversaryDate = DATE(YEAR(StartDate) + Tenure, MONTH(StartDate), DAY(StartDate)) -- Determine Monthly Accrual Rates VAR MonthlyAccrualBefore = SWITCH( TRUE(), Tenure < 2, 10 / 12, Tenure < 10, 15 / 12, 20 / 12 ) VAR MonthlyAccrualAfter = SWITCH( TRUE(), Tenure + 1 < 2, 10 / 12, Tenure + 1 < 10, 15 / 12, 20 / 12 ) -- Determine Effective Date for New Joins VAR EffectiveDate = IF(StartYear = CurrentYear, IF(StartDay <= 15, DATE(CurrentYear, StartMonth, 1), EOMONTH(StartDate, 0) + 1), DATE(CurrentYear, 1, 1) ) -- Calculate Months Before and After Anniversary VAR MonthsBeforeAnniversary = IF( AnniversaryDate <= CurrentDate, DATEDIFF(EffectiveDate, AnniversaryDate, MONTH), DATEDIFF(EffectiveDate, CurrentDate, MONTH) ) VAR MonthsAfterAnniversary = IF( AnniversaryDate <= CurrentDate, DATEDIFF(AnniversaryDate, CurrentDate, MONTH), 0 ) -- Calculate Total Accrued Vacation VAR AccruedVacation = IF( DAY(AnniversaryDate) <= 15, (MonthlyAccrualBefore * MonthsBeforeAnniversary) + (MonthlyAccrualAfter * (MonthsAfterAnniversary + 1)), (MonthlyAccrualBefore * (MonthsBeforeAnniversary + 1)) + (MonthlyAccrualAfter * MonthsAfterAnniversary) ) -- Adjust for the specific case where the anniversary date is after the 15th of the month VAR FinalAccruedVacation = IF( DAY(AnniversaryDate) > 15, (MonthlyAccrualBefore * MonthsBeforeAnniversary) + (MonthlyAccrualAfter * MonthsAfterAnniversary), AccruedVacation ) -- Ensure correct calculation for milestones within the current year VAR CorrectedAccruedVacation = IF( Tenure = 1 || Tenure = 9, (MonthlyAccrualBefore * (MonthsBeforeAnniversary + 1)) + (MonthlyAccrualAfter * MonthsAfterAnniversary), FinalAccruedVacation ) RETURN ROUND(CorrectedAccruedVacation, 2)
Solved
Anonymous
1 year ago Place DAX Commands and Tips
1.6KViews
0likes
6Comments
Calcualting repeat cusotmers for each month and then totaling it up for all months
Hi, I have a requirement where for each month starting six months ago until current selected month, it should calculate the repeat customers that purchased in any previous months and then total up the repeat customers for all six months. For example There are ten customers, A, B, C, D, E, F, G, H, I, J. Customer A, B, C, D and E purchased a product in December. In January, we have D, E, F cusotmers so we have two repeat customers. In Feb, A, H and I purchased. Since A, purchased previously (Dec), we have 1 repeat cusotmer in Feb. In March, we have B, E, H and J so we have three repeat customers and so on. We calculate this until current month. Eventually, we total up the repeat for all months. How can I calcualte this using DAX?
Solved
syedshah01
2 years ago Place DAX Commands and Tips
784Views
0likes
2Comments
okay icon
I have the following table. And I have the following matrix look The "Planned" measure is: CALCULATE( SUM( fac_NFs_SFs[valor_liquido_nf] ), USERELATIONSHIP( dim_Calendario[Data], fac_NFs_SFs[data_previsao_recebimento] ) ) The "Achieved" measure is: CALCULATE( SUM( fac_NFs_SFs[valor_liquido_nf] ), USERELATIONSHIP( dim_Calendario[Data], fac_NFs_SFs[data_recebimento] ) ) The months "Mar", "Mai" and "Jun" = dim_calendario[mês] I need that when I have a receipt date, all the month contexts have the Okay icon, ignoring the month of the column context. I can't do this because the forecast sum uses the forecast column and the received sum uses the received date column. What do I do?
Edgar_massa
2 years ago Place DAX Commands and Tips
1.1KViews
0likes
3Comments
Need help correcting my easy measure
Hi, I need Major, Minor, and Moderate to be their own measures if possible so I can use those details in other calculations. But first I need to isolate them. I could have sworn I've made a measure kind of like this before, but it doesn't work so something must be wrong: Major = CALCULATE(SUM('Table'[Clientcount]), 'Table'[Severity]="Major") Here's my data set: Name Severity Clientcount Dave Major 1 Mark Minor 1 Phil Moderate 1 Stacey Major 1 Emily Major 1 Anna Major 1 Is there a measure that can isolate each severity type and the number of Clientcount that belong to that severity type like this?: Measure 1: Major = 4 Measure 2: Minor = 1 Measure 3: Moderate = 1 Thanks
Solved
RichOB
2 years ago Place DAX Commands and Tips
800Views
0likes
3Comments
Discount Days of one Specific Date Column with Networkdays
Hi guys! Please, I need this answer a lot. I have a Power BI Column "Date" and I need discount 7 days from this specific date take off satuday, sunday and others holidays dates in a Holiday table. Example: Date Column (discount days in formula) Result in a new Date Column 26/06/2024 -7 Days (networkdays) = 16/06/2024 Is it possible? rajendraongole1 Idrissshatila amitchandak mark_endicott
Solved
wendereis
2 years ago Place DAX Commands and Tips
3.4KViews
0likes
19Comments
Alarm Log Page
Hi @v-yohua-msft , This question is next to "Alarm Log, Start Time & End Time in Power BI Visual" which you have already answered. Can you help me to get a single instance rather than all the records of Low Temp and same Clear Time for all. Alarm Log Table should contain: Start Time: 09-02-2024 11:29 End Time: 09-02-2024 11:36 Date Time Asset State Number Alarm Category 09-02-2024 10:57 A 2 Clear 09-02-2024 11:29 A 101 Low Temp 09-02-2024 11:30 A 101 Low Temp 09-02-2024 11:31 A 101 Low Temp 09-02-2024 11:32 A 101 Low Temp 09-02-2024 11:34 A 101 Low Temp 09-02-2024 11:35 A 101 Low Temp 09-02-2024 11:36 A 2 Clear 09-02-2024 11:37 A 2 Clear 09-02-2024 11:38 A 2 Clear You have provided below DAX to create a table: Alarm Log = ADDCOLUMNS( FILTER( 'Table', 'Table'[State Number] <> 1 && 'Table'[State Number] <> 2 && 'Table'[State Number] <> 3 ), "Start Time", 'Table'[Date Time], "End Time", CALCULATE( MIN('Table'[Date Time]), FILTER( ALL('Table'), 'Table'[Asset] = EARLIER('Table'[Asset]) && 'Table'[Date Time] > EARLIER('Table'[Date Time]) && ('Table'[State Number] = 1 || 'Table'[State Number] = 2 || 'Table'[State Number] = 3) ) ) ) Thank You in Advance.
Solved
adarshmp8998
2 years ago Place DAX Commands and Tips
5.6KViews
2likes
4Comments
Show actual counts and 0 values in calculated table column (to identify the gap in products)
Hi i have the following table where we have the possibilities for the same ID to be assigned a grade multiple times in the dataset. id grade (A,B,C,D) count 1 A 1 1 B 2 1 C 2 2 C 1 2 D 1 2 B 1 However looking at this example, i need to show that there are some gaps, so that we can introduce product recommendations. I need to create a calculated table or a column that will show me the nulls or 0 values as well in each category where we don't have a product count for example 1D has nothing, and 2A. id+grade count (with 0 or nulls) 1A 1 1B 2 1C 2 1D 0 2A 0 2B 1 2C 1 2D 1 I have tried the following measures but is not working in a calculated column: CountID = var vCountGrades = CALCULATE( COUNTROWS('Products'), FILTER( 'Products', 'Products'[ID] = EARLIER('Products'[ID]) && 'Products'[grade]= EARLIER('Products'[grade]) ) ) RETURN IF(ISBLANK(vCountGrades ), 0, vCountGrades )
Solved
PatrickNahayo
2 years ago Place DAX Commands and Tips
715Views
0likes
3Comments
Continuously transacting client
Dear All, Need help with a dacx function i got list of transactions for like last three years columns have cust id, month (DD/MM/YYYY), transaction amount etc, it is linked to a date table through month Now i need to know which customers have been continously transacting for atleast last 3 months really struggling with this
Solved
MNMKJ
2 years ago Place DAX Commands and Tips
607Views
0likes
1Comment
Forecast Last 6 Months using the last forecast
Hi! Im struggling with forecasting in Power BI. I have a table with dates, Attributes and amounts. My idea is to calculate the amount for the nexts months based on the last 6 values I have. For the next the calc should consider the 5 actual months and the forecast done, for example: (dummy data) in this case, april should be the average of the last six months, and May should be the average of last five filled and April that was just calculated.
luismolle
2 years ago Place DAX Commands and Tips
533Views
0likes
1Comment
UREGNT!! Ranking measure works to filter table but not funnel chart
Hello, Power Bi community, I'm using the below code to calculate the ranking of categories based on the count of the sample measure. Ranking Category = RANKX(ALLSELECTED('Data Dict - Filters - Category'[Column Names]), [CountSample_Ranking_Catgeory], , DESC, Dense) It is giving me the ranking as below: But I'm trying to use this measure as a visual level filter to filter the data for funnel chart to give me data only for the first category But it's not filtering the data at all. And when I set the visual level filter to "2", the funnel chart just disappears. When the Ranking Category measure is not applied: When the Ranking Category measure = 1 : When the Ranking Category measure = 2 : Here is the link to the Power BI sample dashboard: https://drive.google.com/file/d/1_0Lii0ssjPCUagj9f0zFD7LCUEU4Czfb/view?usp=sharing Please take a look and let me know if there is any solution to this. It is very crucial for my project. Thanks! Look forward to your replies. @MFelix , @amitchandak , @Greg_Deckler , @tamerj1 , @Jihwan_Kim , @Joe_Barry , @Ashish_Mathur,
Anonymous
2 years ago Place DAX Commands and Tips
520Views
0likes
2Comments