Forecast the sales revenue over time based on start and end date

[larbond]

I try to forecast the sales revenue over time. I made some progress, but the graph shows an incorrect values.  The graph shows a value of 10,99, but the total amount of January should be 340. In case of Sales ID I expect January 340 Februari. 

I have the following tables:  

• Date
• Services

The table Dates contains the following columns

• Date (all dates from 1-1-2017 till 1-1-2019)
• Calculatedaterevenue
• Measure (calculation see below)

The table Services contains the following columns

Calculations

Measure = 
	CALCULATE(SUM(Services[RevenuePerBillingDay]);
		FILTER(Services;
			COUNTROWS(
				FILTER(
					VALUES(CalendarDate[Date]);
			            Services[StartDate]<=CalendarDate[Date] &&
			            Services[EndDate]>=CalendarDate[Date])
			)>0
			)
		)

BillDays = CALCULATE(
	COUNT(CalendarDate[Date]);
	DATESBETWEEN(CalendarDate[Date];Services[StartDate];Services[EndDate]))

RevenuePerBillingDay = Services[Revenue]/Services[BillDays]

[v-shex-msft · Accepted solution]

Hi @larbond,

You can try to use below formula to get the summary daily payment:

Total = 
VAR current_id =
    MAX ( Records[ID] )
VAR current_date =
    MAX ( 'Calendar'[Date] )
VAR curretn_Pay =
    CALCULATE (
        MIN ( Records[PerDay] ),
        FILTER (
            ALL ( Records ),
            current_date IN CALENDAR ( [StartDate], [EndDate] )
                && [ID] = MAX ( Records[ID] )
        )
    )
RETURN
    curretn_Pay * COUNT ( 'Calendar'[Date] )

Notice: my formula only works when you limit date range to specific  year, if you not choose the specific year, it will return wrong result.

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @larbond,

You can try to use below formula to get the summary daily payment:

Total = 
VAR current_id =
    MAX ( Records[ID] )
VAR current_date =
    MAX ( 'Calendar'[Date] )
VAR curretn_Pay =
    CALCULATE (
        MIN ( Records[PerDay] ),
        FILTER (
            ALL ( Records ),
            current_date IN CALENDAR ( [StartDate], [EndDate] )
                && [ID] = MAX ( Records[ID] )
        )
    )
RETURN
    curretn_Pay * COUNT ( 'Calendar'[Date] )

Notice: my formula only works when you limit date range to specific  year, if you not choose the specific year, it will return wrong result.

Regards,

Xiaoxin Sheng

[larbond · Accepted solution]

Finnaly, it works with an example of SQLBI.

https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/