Combining Past Sales and Projected Sales

[PaulDBrown]

Good Morning everyone,

I would really appreciate some guidance as to how to resolve my current issue. I have a model including sales and projected sales tables, with a period table relating both to solve the different granularity issue.

What I'm trying to do is to create a measure (or do I need a new table for this?) which will compute a sales or projected sales value depending on the month. In other words, if we are tracking this year's performance, we would want to include sales data for months Jan to March, and projected sales data for this (April) and upcoming months.

So far I seem to have succeeded (after many hours or trial and error) to produce a measure which seems to work AS LONG AS you the time axis is constant; if I try to summarize the data into quarters for example, then subtotal is not what I'm looking for.

Here is a screenshot:

And this is the measure with the period table to identify previous, current and future months:

From what I see, the measure is actually delivering what I'm after on a month to month basis, but at the subtotal level, the measures compute the Projected sales value for JAN-March as opposed to the real sales value. So I'm understanding that the measure is computing a non-referenced month as the projected or ">2" part of the measure.

Is there a simple way to solve this?

Is the best way to generate this information to create a new table, and use the IFs in the measure in a calculated column?

Many thanks for any help with this.

Best regards,

Paul.

PS, I did try to the search function but was unsuccessful.

[Anonymous]

Hi @PaulDBrown,

Can you share a sample file to test or provide more detail content? I'm not so sure for your measures which used in the formula.

Regards,

Xiaoxin Sheng

[PaulDBrown]

Hi @Anonymous

Thank you for taking time to help me with this! I am at present stuck with the project I'm working on becasue of this problem.

Here is the link:

Sales and projection example

The sales values need to be  upto and including March; the projected values need to be  from April onwards.

Basically what I'm trying to do is create a new "running total" combining past sales with future "projected sales or forecasts/estimates". The idea is that a user can understand how these estimates affect the overall performance of the company looking into the future  (even if it is only the coming month).

As you can see in the table in the example, the values in for the individual months seem to be working: I get sales figures for Months Jan-March, and projected values from April onwards.

However the subtotals do not match; the values displayed are always the subtotals for projected, when what I need (in order to be able to summarize by quarter for example, or create groups by quarter in a chart with filter down options) is that these subtotals also respect the timeframe condition: Subtotal Jan-March is the sales value; subtotal form April onwards is the projected value.

Thnak you again for your time.

Best regards,

Paul.

[Anonymous · Accepted solution]

Hi @PaulDBrown,

You can try to use below measure to deal with the total row calculate:

Total Measure(Row Count) = 
if(COUNT('Table'[Date])=COUNTX(ALL('Table'),[Date]),
 "All", 
 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])),[Date]),
	 "Year Level Total",
	 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])),[Date]),
		 "Quarter Level Total",
		 	 if(COUNT('Table'[Date])=COUNTX(FILTER(ALL('Table'),[Date].[Year]=MAX([Date].[Year])&&[Date].[QuarterNo]=MAX([Date].[QuarterNo])&&[Date].[MonthNo]=MAX([Date].[MonthNo])),[Date]),
		 	"Month Level Total","Day Level Total"
		 ))))

You only need to input your formula at the specify "total level" to config the calculation on total level.

Notice: use your columns to replace the different hierarchy level column name.

If above not help, you feel free to post here.

Regards,

Xiaoxin Sheng

[PaulDBrown]

@Anonymous

Thank you so much for taking the time and helping me out. Unfortunately it doesn't seem to work. I have had to adapt the measure to account for the fact that "Projected sales" has a monthly granularity and therefore I need to use the YearMonth period table as a time reference. (Projected Sales table doesn't have individual dates).

The result is what you can see in the "TESTING Total Measure (Row Count) column.

And the sub-totals displayed still display only the values from the projected sales.

Here is the file with the new measure applied:

Current and Projected Sales File Sample

What am I doing wrong?

Thank you again for your help.

Best regards,

Paul.

[PaulDBrown]

@Anonymous

I got it!!

I had to re-formula a couple of measures to get it to work, but SUCCESS!!!

THANK YOU so much for providing the solution! It has helped me enourmously!!

Best regards,

Paul.