Can I Distribute Revenue over time and do a Revenue Forecast?

[Ruta1]

I need some help.

I wanted to calculate a revenue forcast based on a revenue distributed over time (called "Revenue") with this code, 

Forecast = CALCULATE((CalendarTable[Revenue]),SAMEPERIODLASTYEAR(CalendarTable[Date])), 

but i am not getting any results.

To demonstrate the problem I made a new column called "Revenue Forecast" in the same table with this code (I am also only interested in a forecast of 2023 btw, hence the if-filtering):

Revenue Forecast = IF([Forecast]=BLANK() && CalendarTable[Date]>LASTDATE(LoggLeieListe[LeidTil]),[Forecast],0)

As you can see in the picture below, the reveniew prints as blank, even though there is no error with the code.

"Forecast" and "Revenue" is also located in the "CalendarTable" as measures.

In the "LoggLeieListe"-table, which the "Forecast"-messure is based on, every rental-contract is registrered with an unique id ("Id"), start date ("LeidFra"), end date ("LeidTil") and a total revenue for the rental periode. In the same table I have calculated a new column to calculate the "Daily Revenue Per Rental" (Total Revenue / Days in Rental Periode) to go with the dates in the CalendarTable. I use all this to calculate the total revenue (of every rental-contract) per date in the CalendarTable by the following code:

Revenue = CALCULATE(sumx(SUMMARIZE(filter(CROSSJOIN(LoggLeieListe,CalendarTable),CalendarTable[Date]>=(LoggLeieListe[LeidFra]) && CalendarTable[Date]<=(LoggLeieListe[LeidTil])),LoggLeieListe[Id],'CalendarTable'[Date],"_sum",SUM(LoggLeieListe[Daily Revenue Per Rental])),[_sum]))

This code is the only coding i have found that works for my purpose. But while the coding works perfectly, I am suspecting that this codes filter, filters out the posibility of using the sameperiodlastyear-function. 

I have tried making "revenue" and "forecast" as columns insted of measures, but then other issues appared (like circular dependency error ect.)

Is it any other way of calculating a forcast than the "sameperiodelastyear"-method? Or do I have to use another code for the revenue-calculation? 

I have tried using this method: https://community.powerbi.com/t5/Desktop/total-project-revenue-distributed-over-time/td-p/451359, but no matter how much i tried (by adjusting the outside "filter"-funcition for example) it would not give me the correct results.

Does anyone know a solution to my problem? Any help is appreciated😀

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Ruta1]

I would like the forecast to show something like this:

Date	Revenue	Forecast
01/01-22	150	0
02/01-22	200	0
03/01-22	250	0
04/01-22	50	0
(...)	(...)	(...)
01/01-23	0	150
02/01-23	0	200
03/01-23	0	250
04/01-23	0	50

I made a sample file now - but was not able to upload a file to this comment, but let me know if you can't use this link to download a copy of it! https://drive.google.com/file/d/12t7X9S4COuVMCL_DqRnPez6SFAmDKdf7/view?usp=sharing 

Ultimatly, I would like some kind of code that allows me to forecast based on data from more than one year ago. I saw a youtubevideo of someone doing it like this:

Revenue Forecast = IF(LASTDATE(CalendarTable[Date])>LASTDATE(LoggLeieListe[EndDate]),

VAR

RevenueLY = CALCULATE(CalendarTable[Revenue],SAMEPERIODLASTYEAR(CalendarTable[Date]))

VAR

Revenue2YA = CALCULATE(CalendarTable[Revenue],DATEADD(CalendarTable[Date],-2,YEAR))

VAR

Revenue3YA = CALCULATE(CalendarTable[Revenue],DATEADD(CalendarTable[Date],-3,YEAR))

var

factor = 1.05

return

DIVIDE(RevenueLY+Revenue2YA+Revenue3YA,3,0)*faktor

,0)

But that did't work for me either - any suggestions on that matter at well?

Thank you!

[v-yiruan-msft]

Hi @Ruta1 ,

I updated your sample pbix file(see attachment), please check if that is what you want. Please update the formula of measure [Forecast] as below:

Forecast =
CALCULATE (
    CalendarTable[Revenue],
    SAMEPERIODLASTYEAR ( CalendarTable[Date] ),
    ALL ( 'CalendarTable' )
)

In addition, you can refer the following links to get the values on same period last N years...

How to calculate measure for same period 2 years ago

Same period 2 years ago

RunningTotal2LY = 
VAR curYrMinDate  = MIN( 'Time'[PK_Date])
VAR curYrMaxDate  = MAX( 'Time'[PK_Date] )
VAR minDate = DATE( (YEAR( curYrMinDate ) -2 ),MONTH(curYrMinDate), DAY(curYrMinDate))
VAR maxDate = DATE( (YEAR( curYrMaxDate  ) - 2 ), MONTH(curYrMaxDate), DAY(curYrMaxDate))
RETURN
CALCULATE(
    [Total Sales],
    FILTER(
        ALLSELECTED('Time'[PK_Date]),
        'Time'[PK_Date] > minDate && 'Time'[PK_Date] < maxDate
    )
)

Same Period Last N Years

Best Regards

[Ruta1]

Yes, thank you, that worked!

I actually have another problem as well, I cannot calcuate a running total (cumulative total) based on the revenue or forecast using quick measure now.

I want to be able to see better visualy if the revenue is "on track" with the forecast, and I therefore need something like this first to visualize better:

Date	Revenue	Forecast	Cumulative Revenue	Cumulative Forecast
01/01-22	150		150	0
02/01-22	200		350	0
03/01-22	250		600	0
04/01-22	50		650	0
01/01-23		150		800 (650+150)
02/01-23		200		1000
03/01-23		250		1250
04/01-23		50		1300

I have also tried making the revenue into a column and use the function sum, but I got the same result. Any suggestions on how to do it? 🙂

[v-yiruan-msft]

Hi @Ruta1 ,

You can create two measures as below to get it, please find the details in the attachment.

Cumulative Revenue = 
SUMX (
    FILTER (
        ALLSELECTED ( 'CalendarTable' ),
        'CalendarTable'[Date] <= SELECTEDVALUE ( 'CalendarTable'[Date] )
    ),
    [Revenue]
)

Cumulative Forecast = 
IF (
    ISBLANK ( 'CalendarTable'[Forecast] ),
    BLANK (),
    [Cumulative Revenue]
        + SUMX (
            FILTER (
                ALLSELECTED ( 'CalendarTable' ),
                'CalendarTable'[Date] <= SELECTEDVALUE ( 'CalendarTable'[Date] )
            ),
            [Forecast]
        )
)

Best Regards

[Ruta1]

Nice, thank you! 

But when I change this column into a line chart, it goes blank. I want my line chart to look something like this picture:

Why is it blank, when everything seems to be ok?

[v-yiruan-msft · Accepted solution]

Hi @Ruta1 ,

You can create another new measure as below and put this measure onto the line chart to replace the original measure [Cumulative Forecast]. Please find the details in the attachment.

Measure = SUMX ( ALLSELECTED ( 'CalendarTable'[Date] ), [Cumulative Forecast] )

Best Regards