DAX to flatten incoming sales forecast by month with correct subtotals
I have a dataset that projects incoming sales forecast but the day of the month the forecast is projected for is not accurate to when the sales will actually occur. I want to "smooth" the forecast data by calculating the average for the month and assigning it to each day. The ultimate goal is to be able to calculate totals based on past real data and future forecast data and be able to have the subtotals calculate correctly even when "today" is within the summarized period of time. At the moment I am able to get the "Daily Average Forecast" and assign it to the first day of the month, but I am struggling to have it apply to each day of the month and to get the sub-total to be a sum of the previously calculated daily values. I would share code I have written so far but I have tried so many permutations of SUMMARIZECOLUMNS and SUMX that I can't remember what worked best already. I am using Melissa de Korte's Extended Date Table and have Time Intelligence switched off in my model and implicit calculations are disabled.
SUMX is not showing calculating any totals
I have the following measure: 1. Project Load = var result = CALCULATE(SUMX(SUMMARIZE(Project,Project[Project Number]),SUM(Project[Project Value])),ALL('Month Table with weeks')) return result 2. monthly_project_value = CALCULATE( sumx( FILTER('Month Table with weeks', DATE(YEAR(CALCULATE(MIN(Project[Project Start]), ALL('Month Table with weeks'))), MONTH(CALCULATE(MIN(Project[Project Start]), ALL('Month Table with weeks'))), 1) <= MIN('Month Table with weeks'[First day of Month]) && DATE(YEAR(CALCULATE(MIN(Project[Project End]), ALL('Month Table with weeks'))), MONTH(CALCULATE(MIN(Project[Project End]), ALL('Month Table with weeks'))), 1) >= MAX('Month Table with weeks'[First day of Month] ) ), [Project Load] ) ) The filter is used to get the project value for a selected month if the project is live during that month else to not show anything. Although the individual rows are coming correctly but the total is not getting calculated and shows empty.
