Calculated columns for previous weeks values
Hi guys, I have a long table of weekly shares (Share) of several products (SKU) in different regions (Region) as below with the yellow column names. I wish to add 3 calculated columns which present the previous week (-1 WEEK) share of the same product in the same region - same thing for the week before (-2 WEEK) and before that (-3 WEEK). Please help. Thanks
Error when subtracting a calculated column with a measure
Hello. I have an issue where I have a column, lets call it "Gap", that takes a measure ("Measure A") and subtracts a calculated column, ("Column B"). When I do this, the calculation that I am looking for is incorrect. When I put in: Gap =Measure A-Column B and Measure A=24 Column B= 20 So the answer would be 24-20=4. However the answer Power BI is giving me is -19. Why is the table that I have these columns in showing the gap column to have -19. All other calculations are incorrect. Could you please explain to me why its doing this when I have a Measure be subtracted by a column? The reason I have a Measure in the first place, is because I have it synced with a slider to allow the user to change around the weights. Please let me knowCalculated column with cumulative count of repeating dates
I have the following calculated table: I've been trying to add to that table a calculated column that runs a cumulative count of the repeating dates from the date column ([Data]) which should (obviously) reset back to 1 every time a new date comes by. The closest I could get was with the code below, but it still does not produce the results I want, which I hardcoded in red for better understanding of what I really need. Any help on this one is greatly appreciated!Max date by group, rewrite measure to create calculated column
I have the following calculated table from this code: Tbl = SUMMARIZECOLUMNS( dAssets[Ticker], dDates[Date], "Purchases", [Shares purchased], "Sales", [Shares sold] ) Then I created the following measure that retrieves the last purchase date prior to each sale row... Last purchase date before respective sale = VAR Ticker_Ref = MAX( Tbl[Ticker] ) VAR Date_Ref = MAX( Tbl[Date] ) RETURN MAXX( FILTER( ALLSELECTED( Tbl ), [Ticker] = Ticker_Ref && [Date] <= Date_Ref && [Purchases] <> BLANK() ), [Date] ) ... which works just fine when dropped to a table visual: I tried to produce those same results above as a calculated column but I keep getting wrong results. How can I get that to work? Since I'm a DAX newbie, I still have a hard time reproducing a measure code as a calculated column or vice-versa... The closest I got was this... Last purchase date before respective sale = VAR Date_Ref = MAX( Tbl[Date] ) RETURN CALCULATE( MAX( Tbl[Date] ), ALLEXCEPT( Tbl, Tbl[Ticker] ), Tbl[Date] <= Date_Ref, Tbl[Purchases] <> BLANK() ) ... but it returns wrong results (max purchase row date per group of tickers, instead of max purchase row date within each group of tickers prior to or up to each respective sale row date): And before one suggests I should think long and hard before resorting to calculated columns as opposed to measures: I've already done such philosophical conundrum and realized that for this particular application, which is producing that piece of calculation, and others that follow, in order to reach a particular (and very complex) calculation goal, I have no other choice but go with calculated columns.
