Previous Cost of a product for the selected date.
Hi All, I have a tabel with Product, cost, quantity,filedate,brand. I want to calculate previous date cost based on the filedate column. My calculation is not giving me correct result for cost. I have calculated current cost which takes minimum of a product on the selected date for the brand. Below is the table. Product Cost Qty filedate Brand product1 $101 320 31/10/2023 kellogs product8 $721 80 31/10/2023 kellogs product2 $1500 50 29/10/2023 parle product3 $360 150 28/10/2023 parle product4 $46 980 26/10/2023 kellogs Below are my measures. Measure 1 CostMeasureMin = VAR SelectedDate = SELECTEDVALUE ( 'all_disti_og'[filedate] ) VAR TodaysDate = TODAY () VAR FilteredDate = IF ( ISBLANK ( SelectedDate ), TodaysDate, SelectedDate ) VAR CostOnFilteredDate = CALCULATE ( MIN( all_disti_og[cost] ), 'all_disti_og'[aq_vendor] IN VALUES ( 'all_disti_og'[aq_vendor] ) ) RETURN CostOnFilteredDate Measure 2 y date = VAR seldate = SELECTEDVALUE(all_disti_og[filedate]) VAR Prevdate = CALCULATE( MAX(all_disti_og[filedate]), FILTER( ALL(all_disti_og), all_disti_og[filedate] < seldate ) ) VAR Result = CALCULATE( MAX(all_disti_og[filedate]), FILTER(ALL(all_disti_og),all_disti_og[filedate] = Prevdate) ) RETURN Result ---------------------------------------------- Based on measure 2 i want to get previous cost but could not get it. Then i want to get the cost difference from previous and today. Need your expertise to resolve it.
FIRSTNONBLANK to get the previous row value is not working as expected
I am trying to get the TRUE/FALSE value of the previous row. I have read to be able to do this for a row that isn't a number I need to use FIRSTNONBLANK with a 1 as the expression. However, for some reason this works for a bit then just wants to stick to a single value. I have used the following DAX to get to this: = CALCULATE ( FIRSTNONBLANK ( 'Sample Data'[Oil Changed],1 ), FILTER ( 'Sample Data', 'Sample Data'[Sample Date] < EARLIER ( 'Sample Data'[Sample Date] ) && 'Sample Data'[Unit Number] = EARLIER ( 'Sample Data'[Unit Number] ) ) ) This is the output, plus what I actually want in the end column. Does anyone know how I can do this? Power BI example file below. Get Previous Value Just as a side rant, there really needs to be a better way of doing something simple like this. In Excel, it is literally "=[cell you want to reference]" in a table and it's done for the whole column. In DAX this is several lines of code...
