Forum Discussion
Total based on month
Hi guys,
I have two tables, Forecast and Sales. Based on FORECAST PLANNING MONTH column, I would like to show to Total like this.
Sample data is in uploaded PBIX.
Thank you in advance for looking into this. Need this asap please!
3 Replies
I _think_ this is what you want but the numbers don't match. Please validate.
Act + Fcst = var p = SELECTEDVALUE('Sales Forecast'[Forecast Planning Month]) var s = SELECTEDVALUE('Sales Forecast'[Sales Forecast Month]) return switch(TRUE(), HASONEVALUE('Sales Forecast'[Sales Forecast Month]) && s = EDATE(p,1),CALCULATE(sum('Actual Sales'[Actual Sales Qty]),ALL(dimDate),dimDate[Start of Month]<=s), HASONEVALUE('Sales Forecast'[Sales Forecast Month]),sum('Sales Forecast'[Sales Forecast Qty]), var m = min('Sales Forecast'[Sales Forecast Month]) return CALCULATE(sum('Actual Sales'[Actual Sales Qty]),ALL(dimDate),dimDate[Start of Month]<=m)+CALCULATE(sum('Sales Forecast'[Sales Forecast Qty]),'Sales Forecast'[Sales Forecast Month]>m))
