Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mb0307
Responsive Resident
Responsive Resident

Total based on month

Hi guys,

 

Download PBIX File 

 

I have two tables, Forecast and Sales.  Based on FORECAST PLANNING MONTH column, I would like to show to Total like this.

 

Monthly Total.png

Sample data is in uploaded PBIX.

 

Thank you in advance for looking into this.  Need this asap please!

3 REPLIES 3
lbendlin
Super User
Super User

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))

 

 

mb0307
Responsive Resident
Responsive Resident

@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:   

2022-08-10_09-40-40.png

You can change the measure accordingly (remove the first option in the switch)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.