Fill in blanks with last non blank value (using DAX measure)
Hi guys, Does anyone know how to fill in blank values with the previous non blank value? I need this to be done with a dax measure because of my data model, as I'm using a date dimensional table. The measure I'm currently using is this one: PrecoMedioMovelv2 = CALCULATE( LASTNONBLANK(MaterialAvaliacao[PrecoMedioMovel];MaterialAvaliacao[PrecoMedioMovel]); FILTER(ALL('Date'); 'Date'[Date] <= MAX('Date'[Date])) ) But the problem is that it always returns the max value of the column MaterialAvaliacao[PrecoMedioMovel] for the given date context, as you can see in the picture below: The desired output is the following: Here's also the data model I'm using (for the current exercice, table MaterialMovimento doesn't need to be considered) Thanks in advance!!
Calculating Commission on Sales
My objective is to present a report which shows each agent’s name, the total sales amount for that agent for the month and the commission earned based on the commission policy. Here is more context for you: Based on the document, the Commission Policy tells you how an agent's commission is calculated. To calculate an agent's commission you need to know what the agent's monthly salary is and how much sales they made for the month for the different services (application hosting, web services, infrastructure hosting). Once you have the agent's monthly salary and total monthly sales generated, you need to calculate the Salary Cover = Total monthly sales/Agent's monthly salary. Say for example, we have an agent who earns a salary of R1000 per month and the agent generates R1000 of sales for the month (of which application hosting = R500, web services = R300 and infrastructure hosting = R200). Salary cover = total monthly sales/agent's salary = R1000/R1000 = 1. This means that according to the commission table provided, the agent falls into the first category because salary cover <= 1. Based on the commission percentages in the table for the first category, the agent's commission for the month would then be R500*10% + R300*15% + R200*7% = R109 Let's say instead of R1000, this agent made R2000 worth of sales for the month (application hosting = R1000, web services = R600 and infrastructure hosting = R400). Then salary cover = total sales/agent's salary = R2000/R1000 = 2. This means that the agent would fall into the fourth category since salary cover >= 2. The agents commission would then be R1000*100% + R600*100% + R400*50% = R1800 Commission is calculated on 1 month's service revenue. So if an agent sells an annual product of 500, commission is not calculated on 500, but on 500/12. SSL commission is calculated on 2 months revenue and domains are not included. VAT needs to be subtracted before calculating the monthly service revenue as the data is VAT inclusive. Please find data set here: https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true https://docs.google.com/spreadsheet...ouid=104129043494164133703&rtpof=true&sd=true And pbix: https://drive.google.com/file/d/1dGgyQ185t5uJjGZgNPbNyw71jVWvkHLu/view?usp=sharing
DAX in building Rolling 4 Week Average
Hi, I need help in building 4 week rolling average for "Net Change in Price and Cost" with below sample data Change in Price, Change in Cost and Net Change in Price and Cost are all 3 measures. Net Change in Price and Cost = Change in Price - Change in Cost I do have separate date table and the above data shows weekly data and my report has Invoice Date as Filter. I need to calculate rolling 4 week average, here the tweak is that for first week start date 12/07 rolling average should be same while for next 12/14 it should (-4.80% + 0.18%)/2 = -2.31% for 12/21 it should be (-4.80% + 0.18% - 0.04%)/3 = -1.55% for 12/28 it should be (-4.80% + 0.18% - 0.04% -0.65%)/4 = -1.33% for 01/04 it should be (0.18% - 0.04% -0.65% +1.03%)/4 = 0.13% as shown below I have created below DAX but its not working as expected Net Change in Price and Cost 4 Wk Rolling Avg = var start_day = MIN('Date'[Week Start Date])-21 var end_day = MAX('Date'[Week Start Date]) return CALCULATE([Net Change in Price and Cost], DATESBETWEEN('Date'[Week Start Date],start_day,end_day), REMOVEFILTERS('Date'[Week Start Date]))/CALCULATE(DISTINCTCOUNT('Date'[Week Start Date]),DATESBETWEEN('Date'[Week Start Date],start_day,end_day),REMOVEFILTERS('Date'[Week Start Date])) Can someone help me on this??
Selectedmeasureformatstring DAX Calculation Item
Hi everyone, I ran into an error while trying to create a Calculation Group. I have a Measure: As you can see, the data type depends on the CalcType. I created a Test calculation group: With Calculation items: 1) Actual 2) Actual PY 3) Variance PY The Matrix visual works fine with Actual and Actual PY. However, as soon as I created the Variance PY, the visual has this error: It relates to the CalcType=3, FORMAT([PL_%_of_Revenue_Actual],"0.00%") I have been trying to troubleshoot the error. The possible solution is Selectedmeasureformatstring DAX measure in the Dynamic Format String. I am unable how to apply to my Calculation Item measure. Can someone please help? Thanks very muchDAX Polynomial Regression Calculation Issue
I am attempting to implement a 2nd order polynomial regression to fit some data. It appears that I can calculate the coefficients for the regression equation correctly, but I just cannot figure out how to correctly return the correct result of the equation to use in a table/chart. Starting from this solution, I ended up with the following measure: values (poly fit) = // Reference: https://www.thedatascientists.com/polynomial-regression/ // https://metric.ma.ic.ac.uk/metric_public/matrices/inverses/inverses2.html // Because our Xs (dates) and Ys (measures) are in different tables, we have to temporarily create a new table (Known) with both of those values so we can perform SUMX operations easily. // To get the same polynomial fit as excel, the x values in the calculation are masked over with 1 thru n. Thus x-values in the calc are n+1-min(n) var countItems = COUNTX(VALUES(Table1[date]),Table1[date]) var Known = SELECTCOLUMNS ( ALLSELECTED(Table1[date]), "KnownX", [zSequence1ToN], "KnownY", CALCULATE(SUMX(Table1,[values])) ) var sumOfXs = SUMX(Known, [KnownX]) var sumOfYs = SUMX(Known, [KnownY]) var sumOfX2 = SUMX(Known, [KnownX] ^ 2) var sumOfX3 = SUMX(Known, [KnownX] ^ 3) var sumOfX4 = SUMX(Known, [KnownX] ^ 4) var sumOfXY = SUMX(Known, [KnownX] * [KnownY]) var sumOfX2Y = SUMX(Known, [KnownX] ^ 2 * [KnownY]) var determinant = countItems*((sumOfX2*sumOfX4)-(sumOfX3*sumOfX3))-sumOfXs*((sumOfXs*sumOfX4)-(sumOfX2*sumOfX3))+sumOfX2*((sumOfXs*sumOfX3)-(sumOfX2*sumOfX2)) //Calculate the inverse matrix (combining a few steps together) var M11 = ((sumOfX2*sumOfX4)-(sumOfX3*sumOfX3))/determinant var M12 = -((sumOfXs*sumOfX4)-(sumOfX3*sumOfX2))/determinant var M13 = -((sumOfX2*sumOfX2)-(sumOfXs*sumOfX3))/determinant //there was an error on this line i had to add a negative to fix. no idea why. EVERY other var evaluates correctly. var M21 = -((sumOfXs*sumOfX4)-(sumOfX2*sumOfX3))/determinant var M22 = ((countItems*sumOfX4)-(sumOfX2*sumOfX2))/determinant var M23 = -((countItems*sumOfX3)-(sumOfXs*sumOfX2))/determinant var M31 = ((sumOfXs*sumOfX3)-(sumOfX2*sumOfX2))/determinant var M32 = -((countItems*sumOfX3)-(sumOfX2*sumOfXs))/determinant var M33 = ((countItems*sumOfX2)-(sumOfXs*sumOfXs))/determinant //Assuming equation of: a*x^2 + b*x + c var c = M11*sumOfYs+M12*sumOfXY+M13*sumOfX2Y var b = M21*sumOfYs+M22*sumOfXY+M23*sumOfX2Y var a = M31*sumOfYs+M32*sumOfXY+M33*sumOfX2Y RETURN // SUMX( // DISTINCT(Table1[date]), // a * [zSequence1ToN] ^ 2 + b * [zSequence1ToN] + c // ) [a]*[zSequence1ToN]^2+[b]*[zSequence1ToN]+[c] Where [zSequence1ToN] is a RANKX of the date values zSequence1ToN = RANKX(ALLSELECTED(Table1[date]),CALCULATE(SUM(Table1[date])),,ASC) My issue is I cannot figure out how to correctly calculate this for each row in the table. The screenshot below is what I am getting (compared to what I should be getting - at least according to excel. I suspect my issue may be where I am returning the values, but at this point I am out of ideas and need advice. I have attached a sample pbix file and the excel file I was using for validation. What am I doing wrong? https://1drv.ms/u/s!Ah3VDq5HnODQgcArelYadi0cXQQZzA?e=8YdvQK
Calculate Max and Min value of subcategory within category on unpivot data with slicer
Hi, I need categorywise Max and Min value of aggregated average on unpivot data. I attached data and needed result screenshot also. In data, actually I have 3 concepts which have 4 levels each. sq variable with text datawill be in slicer . Utility score needs aggregated as average and categorywise max-min in next column as per screenshot. KIndly help to fix it out, I am not able to use max value on aggregated variable in matrix.cumulative count measure per row
hello i need to create a measure that calculates incentive as per below rules Achievement Incentive 1 to 5 count of sales 2$ 6 to 10 count of sales 4 $ 11 to 15 count of sales 6 $ 16 and above count of sales sales 8 $ what this means, if agent has made 15 transactions, he will get incentive of (2$*5) for first 5 transactions, and (4$*5) for next 5 transactions and (6$ *5) the 5 transactions after. so total incentive= 10+20+30=60$ i created below measure = CALCULATE(IF([MNP cumulative count]<=5,[MNP cumulative count]*2,IF([MNP cumulative count]>=6 &&[MNP cumulative count]<=10,[MNP cumulative count]*4,IF([MNP cumulative count]>=11 &&[MNP cumulative count]<=15,[MNP cumulative count]*6,IF([MNP cumulative count]>=16,[MNP cumulative count]*8,0))))) and [MNP cumulative count]= CALCULATE(SUMX(SUMMARIZE('categories trans','categories trans'[AGENT_ID],'categories trans'[AGENT_NAME],'categories trans'[TRANSDATE],'categories trans'[achievement],'categories trans'[Categoty]),[mnp distinct count]), FILTER ( ALLSELECTED('categories trans'), 'categories trans'[TRANSDATE]<=MAX('categories trans'[TRANSDATE]))) my problem is the cumulitve count does not count correctly when agent has 2 transactions in the same day. instead of counting them as 2 it's counting them as 1 also the data i have is as below table agent id agent name transaction value (achievement) date 123 abc 2 1/11/2023 456 def 1 2/11/2023Calculation 12 months slide
Hello, I want to do a calculation over a rolling period of 12 months on Power BI. I would like to retrieve this measure for each month over a rolling 12-month average measure that I already have. For example, if I select the month of December 2023, I retrieve the last 12 values (from previous months = December 2022) of averages and divide by 12 the whole. I have already try this but it's return me only the [TotalAverage] values : CALCULATE( ([TotalAverage]), DATESINPERIOD('SourceMCP'[Month], MAX('SourceMCP'[Month]), -12, MONTH) )
