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??
Calculate Last Transaction Records
Hi, Within Power BI, I have a below table and I want to create another column which calculates Last Margin % for every records. If the customer A buys product A12 then for its Last Margin % is null and if same customer (i.e. A) buys same product (i.e. A12) then its Margin % will 30%. So my result table will be as below. I want to write DAX for this. Can some one help me on this?Help in Cumulative Total
Hi I am facing issues with Cumulative Total. Below are the DAX I am using for cumulative total. I have below table Week Start Date | Margin | Margin Last Year Week | Difference in Margin I have to take Cumulative of Difference in Margin Margin is measure Margin Last Year Week is measure calculated as Margin Last Year Week = CALCULATE( [Margin], FILTER( ALL('Date'), 'Date'[Week Rank] = MAX('Date'[Week Rank])-52 ) ) Difference in Margin is a measure which is calculated as Difference in Margin = CALCULATE( ([Margin] - [Margin Last Year Week]), FILTER('Date','Date'[Year] = 2022) ) Cumulative Total DAX Margin Diff Cumulative = CALCULATE( [Difference in Margin], FILTER( ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]) ) ) We have neagative values from March as we don't have data from March 2022 (difference is calculated by taking difference of previous year week from current year week in focus.) so Feb cumulative value should be 4997451 + 2398854 = 7396305 ( which is coming as 7494045 which is wrong ) I want to display this Cumulative value in column chart and also I want that it should display till Feb without using any invoice filter as we have data from Jan 2021 to Feb 2022 and data is populated on weekly basis so this graph will gradually increases weekly. Any help will be appreciated. Thanks!
