Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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))
@lbendlin Thanks you much for sending the solution.
Sorry for not being clear but I want to show Forecast values in the table and ONLY change the total (forecast + actuals).
Like red boxes below:
You can change the measure accordingly (remove the first option in the switch)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |