## Desktop

Regular Visitor
Posts: 30
Registered: ‎03-28-2017

# Cumulative Sales vs Sales Forecast in current Month

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 =
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?

Accepted Solutions
Super User
Posts: 1,277
Registered: ‎09-19-2016

## Re: Cumulative Sales vs Sales Forecast in current Month

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

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

Proud to be a Datanaut!

All Replies
Super User
Posts: 1,277
Registered: ‎09-19-2016

## Re: Cumulative Sales vs Sales Forecast in current Month

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

Proud to be a Datanaut!

Regular Visitor
Posts: 30
Registered: ‎03-28-2017

## Re: Cumulative Sales vs Sales Forecast in current Month

[ Edited ]

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.

Super User
Posts: 1,277
Registered: ‎09-19-2016

## Re: Cumulative Sales vs Sales Forecast in current Month

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.

Proud to be a Datanaut!

Regular Visitor
Posts: 30
Registered: ‎03-28-2017

## Re: Cumulative Sales vs Sales Forecast in current Month

i need it to work with the slicer/filter MFelix

Super User
Posts: 1,277
Registered: ‎09-19-2016

## Re: Cumulative Sales vs Sales Forecast in current Month

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

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

Proud to be a Datanaut!

Regular Visitor
Posts: 30
Registered: ‎03-28-2017

## Re: Cumulative Sales vs Sales Forecast in current Month

Thanks MFelix!

- Objectives are linked to the last day of each month

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

Regards,

Super User
Posts: 1,277
Registered: ‎09-19-2016

## Re: Cumulative Sales vs Sales Forecast in current Month

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

...

Proud to be a Datanaut!

Regular Visitor
Posts: 30
Registered: ‎03-28-2017

## Re: Cumulative Sales vs Sales Forecast in current Month

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!

Highlighted
Regular Visitor
Posts: 30
Registered: ‎03-28-2017

## Re: Cumulative Sales vs Sales Forecast in current Month

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