Reply
Regular Visitor
Posts: 26
Registered: ‎03-28-2017
Accepted Solution

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


Accepted Solutions
Super Contributor
Posts: 840
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

 

 

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

View solution in original post


All Replies
Super Contributor
Posts: 840
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

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

Re: Cumulative Sales vs Sales Forecast in current Month

[ Edited ]

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.

 

 

Super Contributor
Posts: 840
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.

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

Re: Cumulative Sales vs Sales Forecast in current Month

i need it to work with the slicer/filter MFelix

Super Contributor
Posts: 840
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

 

 

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

Regular Visitor
Posts: 26
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 Contributor
Posts: 840
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

...

Regular Visitor
Posts: 26
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: 26
Registered: ‎03-28-2017

Re: Cumulative Sales vs Sales Forecast in current Month

 

 

Untitled.png

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