Actual sales through last month and forecast by month through end of year

[jpt1228]

Hello, I searched for quite a while and couldn't find a post close enough to my topic.

I have a fact table that has Forecast and shipments.  I am trying to create a report that would show by month actual shipments for the previous month and then pull in forecast units for the rest of the year by month to show where the year would finish bassed on that data. I have product DIM table with the item number and item price to be able to calculate the gross sales value. In the below example lets assume it is the end of January and I want to see what we would expect to sell from Jan through the end of Feb. However, this would be for the entire year through December. I have a Date DIM table as well.

 

[jpt1228 · Accepted solution]

Hi @v-shex-msft Thank you for your input - I think I probably structure my sample data in a confusing way. What I was looking for is actual sales dollars for the last full month completed, and then bring in forecast from that time to the end of the year. I found this and it seems to work.

[v-shex-msft]

HI @jpt1228,

For your requirement, you should add a condition to check if current date is year end.

Sample:

Previous Amount= 
var currDate=MAX('Fact table'[Date])
return
if(MONTH(currDate)<>12,
	SUMX(FILTER(ALL('Fact table'),FORMAT([Date],"yyyymm")=FORMAT(DATE(YEAR(currDate),MONTH(currDate)-1,DAY(currDate)),"yyyymm")),[Amount]),//previous month amount
	SUMX(FILTER(ALL('Fact table'),YEAR([Date])=YEAR(currDate)-1),[Amount]))//previous year amount

In my opinion, I think matrix visual will more suitable to achieve your requirement, you can put the measure to value fields and add filter to show the previous year at total row.

Previous Value = 
var currDate=MAX('Fact table'[Date])
return
IF(ISBLANK(currDate)=FALSE(),//filter blank row
if(COUNT('Fact table'[Date])=COUNTX(ALL('Fact table'),[Date]),//all table condition
	"All Table",
	IF(COUNT('Fact table'[Date])=COUNTX(FILTER(ALL('Fact table'),[Date].[Year]=YEAR(currDate)),[Date]),//total year condition
		"Total Row",
	CALCULATE(SUM('Fact table'[Amount]),FILTER(ALL('Fact table'),FORMAT([Date],"mmyyyy")=FORMAT(DATE(YEAR(currDate),MONTH(currDate)-1,1),"mmyyyy"))))))

Regards,

Xiaoxin Sheng

[jpt1228 · Accepted solution]

Hi @v-shex-msft Thank you for your input - I think I probably structure my sample data in a confusing way. What I was looking for is actual sales dollars for the last full month completed, and then bring in forecast from that time to the end of the year. I found this and it seems to work.

[jpt1228 · Accepted solution]

I wanted to provide a little more clarity on this calculation - There was a parDimDate table used in the example. I have done a little more work on this and the below calculations will work off the date table and the date today. Any date less than or equal to today will bring the actual invoiced sales, and anything greater than today will bring in forecast sales.

CaseForecastPostDate = CALCULATE(
SUM(FactForecast[Forecast Cases])
,FILTER(
DimDate
,DimDate[Date] > TODAY()
)
)

CaseSalesPreDate = CALCULATE(
SUM(FactInvoicedOrders[Invoiced Cases])
,FILTER(
DimDate
,DimDate[Date] <= TODAY()
)
)

SalesandForecastCases = [CaseSalesPreDate]+[CaseForecastPostDate]

[Oomsen]

I used this the calc my returning costs. I would like to use TODAY-1 month because current month is still running and doesn't includes all the costs. How do i need to change the calc?