calculations

16 Topics

Help Needed with DAX Expression for Current and Future Month Calculations
Hi everyone, I’m working on a DAX expression to calculate a display value for a line chart in Power BI. My goal is to correctly handle data for the current month and future months with the following requirements: For the current month: I need to combine the cumulative sum of actual values with the rolling wave forecast value. For future months: The display value should consist of the cumulative sum of the actual values from the current month plus the forecast value for that future month. Here's the DAX expression I have so far: Display Value = VAR IsCurrentMonth = YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY()) VAR IsFutureMonth = YEAR([Date]) > YEAR(TODAY()) || (YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) > MONTH(TODAY())) VAR CurrentMonthActualCumulativeSum = CALCULATE( MAX([Actual Cumulative.Sum]), FILTER( ALLSELECTED(), YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY()) ) ) RETURN IF( [Series Type] = "Rolling Wave Forecast", IF( IsCurrentMonth, [Actual+Rolling Wave Forecast], IF( IsFutureMonth, CurrentMonthActualCumulativeSum + [Value], [Actual+Rolling Wave Forecast] ) ), [Value] ) The Problem: When I add this measure to a line chart, the current month’s values display correctly. However, for future months, the cumulative sum is incorrectly added multiple times. For example, in the next month, the cumulative sum of the actual values from the current month is added again, and this continues to accumulate incorrectly in subsequent months. What I Need: I need the calculation to add the cumulative sum of the actual values only once for the current month and then add the forecast value for each future month without repeating the cumulative sum. Any guidance on how to fix this issue would be greatly appreciated! Thank you! (Images with captions are below)
Jules102
2 years ago Place DAX Commands and Tips
817Views
0likes
3Comments
How to calculate average profit ONLY when the product has been bought and sold in selected time.
Hello. I need to calculate the average profit of per product and in general in the selected time period. It is for a scrap business that buys scrap and then sells it forward. However, I only want to calculate profits for a product that has been bought AND sold in the selected period, because otherwise it will calculate a loss if a product has been recently bought in the selected time period but not sold. Now I have created a DAX formula that can calculate this on single products. Formula looks like this: Kate keskimäärin = IF( NOT(ISBLANK([Ostot keskimäärin €/T])) && NOT(ISBLANK([Myynti keskimäärin €/T])), [Myynti keskimäärin €/T] - Ostot[Ostot keskimäärin €/T], BLANK() ) Where "Ostot keskimäärin €/T" is average buying price and "Myynti keskimäärin €/T" is average sales price. It works and calculates this on single products but when it comes to the overall average on all the products it calculates also the ones that how been bought but not sold and vice versa. As you can see in this table the total is calculated falsley since it includes everything. Does anybody know how I can get the average profit in total? It doesnt necessarily have to be visible in the table but I need the calculation to show the average profit All help will be appriciated.
Solved
dblw
2 years ago Place DAX Commands and Tips
855Views
0likes
2Comments
EARLIER Function too Slow!
Hi, I currently have the below calculated column and I suspect the EARLIER function is taking most of the memory when I try to refresh. ObjectIndex = CALCULATE ( COUNT ( DATA[ID ), ALLEXCEPT ( DATA, DATA[ID )), DATA[Date )<= EARLIER ( DATA[Date] ) ) ) StatusStreak is dependent on the above. DATA REFERENCE: ID Status Date StatusStreak ObjectIndex XYX COMPLETED 4/15/2023 1 1 XYX COMPLETED 4/16/2023 2 2 XYX FAILED 4/17/2023 1 3 XYX COMPLETED 4/18/2023 1 4 XYX COMPLETED 4/19/2023 2 5 XYX COMPLETED 4/20/2023 3 6 XYX COMPLETED 4/21/2023 4 7 XYX COMPLETED 4/22/2023 5 8 XYX FAILED 4/23/2023 1 9 XYX COMPLETED 4/27/2023 1 10 XYX COMPLETED 4/28/2023 2 11 XYX COMPLETED 4/29/2023 3 12 XYX COMPLETED 4/30/2023 4 13 XYX COMPLETED 5/1/2023 5 14 XYX COMPLETED 5/2/2023 6 15 XYX COMPLETED 5/3/2023 7 16 XYX COMPLETED 5/4/2023 8 17 XYX COMPLETED 5/5/2023 9 18 XYX COMPLETED 5/6/2023 10 19 XYX COMPLETED 5/7/2023 11 20 XYX COMPLETED 5/8/2023 12 21 XYX COMPLETED 5/9/2023 13 22 XYX COMPLETED 5/10/2023 14 23 XYX COMPLETED 5/11/2023 15 24 XYX COMPLETED 5/12/2023 16 25 XYX COMPLETED 5/13/2023 17 26 XYX COMPLETED 5/14/2023 18 27 XYX COMPLETED 5/15/2023 19 28 XYX COMPLETED 5/16/2023 20 29 XYX COMPLETED 5/17/2023 21 30 XYX COMPLETED 5/18/2023 22 31 XYX COMPLETED 5/19/2023 23 32 XYX COMPLETED 5/20/2023 24 33 XYX COMPLETED 5/21/2023 25 34 XYX COMPLETED 5/22/2023 26 35 XYX COMPLETED 5/23/2023 27 36 XYX COMPLETED 5/24/2023 28 37 XYX COMPLETED 5/25/2023 29 38 XYX COMPLETED 5/26/2023 30 39 XYX COMPLETED 5/30/2023 31 40 XYX COMPLETED 5/31/2023 32 41 XYX COMPLETED 6/1/2023 33 42 XYX COMPLETED 6/2/2023 34 43 XYX COMPLETED 6/4/2023 35 44 XYX COMPLETED 6/5/2023 36 45 XYX COMPLETED 6/6/2023 37 46 XYX COMPLETED 6/7/2023 38 47 XYX COMPLETED 6/8/2023 39 48 XYX COMPLETED 6/9/2023 40 49 XYX COMPLETED 6/13/2023 41 50 XYX COMPLETED 6/14/2023 42 51 XYX COMPLETED 6/15/2023 43 52 XYX COMPLETED 6/16/2023 44 53 XYX COMPLETED 6/17/2023 45 54 XYX COMPLETED 6/18/2023 46 55 XYX COMPLETED 6/19/2023 47 56 XYX COMPLETED 6/20/2023 48 57 XYX COMPLETED 6/21/2023 49 58 XYX COMPLETED 6/22/2023 50 59 XYX COMPLETED 6/23/2023 51 60 XYX COMPLETED 6/24/2023 52 61 XYX COMPLETED 6/25/2023 53 62 XYX COMPLETED 6/26/2023 54 63 XYX COMPLETED 6/27/2023 55 64 XYX COMPLETED 6/28/2023 56 65 XYX COMPLETED 6/29/2023 57 66 XYX COMPLETED 6/30/2023 58 67 XYX COMPLETED 7/1/2023 59 68 XYX COMPLETED 7/2/2023 60 69 XYX COMPLETED 7/3/2023 61 70 XYX COMPLETED 7/4/2023 62 71 Is there an alternative to what I'm trying to do without having to impact performance?
Solved
rogerthat
3 years ago Place DAX Commands and Tips
659Views
0likes
1Comment
Unpivot Column with Different Calculation Applied
Hi there, I am trying to unpivot data matrix using pwoer query but the data calculation is not only using summraize but also average since the value have different value format, iie. absolute number and percentage. Is it possible to apply this purpose using unpivot column in power query? Here is the data matrix example: I would like to convert (unpivot) and visualize the table into this: Kindly share your thoughts on this. Thank you in advance!
Solved
Anonymous
3 years ago Place DAX Commands and Tips
913Views
1like
3Comments
Calculating a factor
Hi, I'm trying to calculate the column "Fator" and the calculous is pretty simple. I just want to do this math operation: FATOR = "Previous Factor" * ("Taxa CDI Deslocada"+1)^(("Dias úteis" - "Dias Úteis Deslocada")/252)-1 for the first line "Previous Factor" = 100; for all the other lines "Previous Factor" is the FATOR calculated in the line above. Although it appears to be simple math, I don't know how to write it in Dax.
Solved
ZodtePt
3 years ago Place DAX Commands and Tips
1KViews
0likes
3Comments
Filter per last entry, per month
I'm working with a hospital database to make a dashboard that shows both a daily status and a monthly status, I need to make visuals such as a card with patients per sex, a matrix with patient names and age in months, etc... So far, to filter between who is and isn't admitted anymore, I check if a column called [Discharge] is blank. If there's text, then the patient has left and will no longer show up. This works for the daily version, but it doesn't work for the monthly version, because I'd be leaving out the people who were admitted on a 31st and haven't left. I later started experimenting with solutions I saw here and there and ended up making a chimera of a formula, but to no avail. To count different people on these visuals, I use the same population formula (Note: My database contains various areas, but I am only interested in the fields that say Utin (ICU)): Hospital-ICU_Population = CALCULATE(DISTINCTCOUNT(Hospital-ICU[Episode Number]), FILTER(Hospital-ICU,(Hospital-ICU[Area] == "Utin" || Hospital-ICU[Area] == "Utin*"))) My first approach towards solving this problem was making a new table based on the original data, called CALCULATETABLE(): HOSPITAL-ICU_LASTENTRY = CALCULATETABLE(HOSPITAL-ICU, FILTER(HOSPITAL-ICU,HOSPITAL-ICU[DISCHARGE] <> BLANK()) ) However, like I mentioned, this makes the numbers come out wrong as I'm not counting those who entered on the 31st and are still admitted. Then I tried the following formula that'd return True or False to the fact that that entry was the last entry. However, it returned everything as True... isLastEntry = CALCULATE( MAX(HOSPITAL-ICU[Revision Date]), FILTER(ALL(HOSPITAL-ICU), (HOSPITAL-ICU[Episode Number] = EARLIER(HOSPITAL-ICU[Episode Number]) Later on I added a chunk that I found online, and this seemed to return the right result for Population in the cards, but not for the Matrix, as the patients would show up repeated and their data would sum (And quite frankly, I can't find sense to why it helped). isLastEntry = VAR __Max = MAXX( FILTER( HOSPITAL-ICU, MONTH([Revision Date]) = MONTH(EARLIER([Revision Date])) && YEAR([Revision Date]) = YEAR(EARLIER([Revision Date])) ), [Revision Date] ) RETURN CALCULATE( MAX(HOSPITAL-ICU[Revision Date]), FILTER(ALL(HOSPITAL-ICU), (HOSPITAL-ICU[Episode Number] = EARLIER(HOSPITAL-ICU[Episode Number]) && HOSPITAL-ICU[Revision Date] == __Max))) And that's pretty much it. I am stumped on what other kind of filter or formula to use, as I can't find anything I can think about using on Power BI's documentation, as there is an EARLIER() formula, but there isn't a LATEST() formula. Any help and tips are appreciated. Here I'll add a portion of my data, along with extra info. Here's some sample data My expected result is to see 12 different patients in the month of May that were in the Utin area. 6 men and 6 women. If the data on [Area] has an asterisk, it means there was a change, but the patient is still in this area. Similarly, there are . on [Discharge], these just indicate a change of Area as well, but I just remove them with Power Query so they won't interfere.
Solved
Anonymous
3 years ago Place DAX Commands and Tips
1.3KViews
0likes
4Comments
Power BI Calculation
Hi, Question regarding a calculation in Power BI. The need is to calculate 'Total Hours of Overtime for a given period'. Input variables: 1. Time = date range that is selected through a Slicer. 2. Number of resources 'active' also updated based on the dates selected through the Slicer. 3. Activities will for tasks that exclude any form of Leave. 4. Overtime will be any excess hours more than 45 hours for a week (for all 'active' resources). Looking forward to the forum's response. Thanks and regards, Ruan
Solved
Anonymous
3 years ago Place Desktop
667Views
0likes
2Comments
Percentage met calculation
I have the following data I want to show in the bar chart a %met which will be the total of the met_first = True compare with the total of tickets fr example Dec has 105 = true and 10 = false the total count 115 I want to show the 91% in the bar chart. I am new to power bi so I am not sure ow to get that calculation and if I can get tips what learnings to use to get better at DAX
Solved
liliana3186
4 years ago Place DAX Commands and Tips
1.2KViews
0likes
4Comments
Error in calculation of a KPI
Hi everyone! I'm currently having a weird issue. I've made the following parameters to create a KPI which does this: In one side you have last month avg sales $, calculated by the n of days from the last month: Promedio_Facturacion = SUM('Mes pasado'[FACTURACION])/DAY(ENDOFMONTH('Mes pasado'[FECHA])) Then you have the current month: Promedio Facturacion_act = SUM('Mes actual'[FACTURACION])/DAY(TODAY()) Then I have to calculate the percentage increase/decrease between those 2 numbers: vs Mes Anterior (Facturacion) = ([Promedio Facturacion_act] - 'Mes pasado'[Promedio_Facturacion])/'Mes pasado'[Promedio_Facturacion] Once I have that calculation, I've elaborated a KPI based on the following: KPI = IF([vs Mes Anterior (Facturacion)] <= -0.20, "CRITICO", IF([vs Mes Anterior (Facturacion)] <=-0.10, "ALERTA", IF([vs Mes Anterior (Facturacion)] < 0, "CUIDADO", "OK"))) Problem is that isn't working propperly, for example: First case is -0.7 which is critical, wrong since it has to count from -0.20. But what kills me is that 0.67 is Critical and 0.8 is OK. Its boggling me for sure. Do you have any tips for this? Thanks! UPDATE: I saw that what is causing this issue is that the KPI metric was made as a Column. When I transpose it as a Metric this error doesn't happen, but, you loose the opportunity of using the KPI as a filter for example. Furthermore, when the KPI is used as a column, since this query tracks transactions daily, what happens is that it issues an status for each transaction and, when data is resumed by unique ids, this status sum up displaying the most repetitive ocurrence, not the accorded one. I was thinking maybe a SUMMARIZE would help?
Matias_Tiberio
4 years ago Place DAX Commands and Tips
956Views
0likes
3Comments
Cumulative unique count based on sales values-DAX
Hi expert, I am stucking on to calculate cumulative unique count in DAX month on month basis some criteria, please help me to get this done. Thanks a ton in advance. Icey Tanushree_Kapse VahidDM Anonymous amitchandak pbig administrator PBICommunity PBCommunity Raw data and summary below. Please help me on this. Thanks
Solved
Anonymous
4 years ago Place DAX Commands and Tips
1.3KViews
0likes
4Comments