Forum Discussion

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
9 years ago
Solved

Combining Past Sales and Projected Sales

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.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      PaulDBrown
      Icon for Community Champion rankCommunity Champion
      9 years ago

      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's avatar
        Anonymous
        Not applicable
        9 years ago

        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