Cumulative Sales vs Sales Forecast in current Month

[Raul09]

Hi,

I'm trying to make a linear graph that shows the cumulative sales in April vs the actual sales forecast

First, my cumulative sales formula is: 

Cumulative Sales = CALCULATE(SUM('BD-New'[Amount]); Filter(ALL('Calendar Table'); 'Calendar Table'[Date] <= MAX('Calendar Table'[Date])))

but it isn't working, i don't know if my calendar is ok..

I made a calendar using this formula 

Calendar Table =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
)

maybe that's the problem..

and second, i have a forecast of X amount of usd by the end of the month, how can i insert that in the graph a way that it can be displayed as cumulative projection by day?

Thanks in advance

[MFelix]

Hi @Raul09,

Try to work with the TOTALYTD measure it will allow to make the calculation.

Cumulative Sales = TOTALYTD(SUM('BD-New'[Amount]);'Calendar Table'[Date])

Regards,

MFelix

[Raul09]

Thanks for the reply,

my sales cumulative is working right now, but i still need the cumulative daily goal to show along.

Let's say we have a 500k monthly goal, we are currently in April 18.. april has 30 days, so we have 12 days left.

My daily goal is 16,6k and i want to show that in the same graphic as a linear value along my actual daily sales cumulative.

how can i do that?

Thanks.

[MFelix]

One question do you want the calculation for the goal to be based on current date or on a date selected in a slicer/filter.

[Raul09]

i need it to work with the slicer/filter MFelix

[MFelix · Accepted solution]

Hi @Raul09,

I made the assumptions below to make this owrk please tell me if any of this is incorrect:

- Slicer is in Date table

- Objectives are in a separated table

- Objectives are linked to the last day of each month

- Table Dates link to table Sales and Objectives by column date

Please do as follow

1 - In table objectives create a new column and a new measure withthe following formulas

COLUMN =  Days_Month = DAY(Objective[Date])
MEASURE= Daily_Total = DIVIDE(SUM(Objective[Objective]);SUM(Objective[Days_Month]))

This will return the number of days in the month

2 - Create in Table Sales the measure below

Obj_YTD = TOTALYTD([Daily_Total]*DAY(MAX('Calendar Table'[Date]));'Calendar Table'[Date])

Add the Obj_YTD to you visual and you will have the total you need.

Regards,

MFelix

[Raul09]

Thanks MFelix!

- Objectives are linked to the last day of each month

i don't have this, can you elaborate any further?

Regards,

[MFelix]

What is the data for your objectives? In my example I did it like this

Date                Objective

31/01/2017     500

28/02/2017     500

31/03/2017     500

...

[Raul09]

Wow! is working now, thanks! really!!

but my sales cumulative stopped working since i've changed the slicer date from my sales table date to the calendar table date

The formula is: 

Sales Cumulative =

CALCULATE (
SUM ('BD-NEW'[Amount]);
FILTER (
ALL (DimDate[Date]);

i need it to work with the slicer/filter too

appreciate the help!

[Raul09]

The graphic is looking like this now, cumulative sales isn't working with the slicer/filter

[MFelix]

Change the ALL from the cumulative to ALLSELECTD formula, should work with the filter/slicer.

Regards
MFelix