Spread Revenue across remaining months in FY

[ankitp]

Hi

I want to be able to show the expected revenue across relevant months from potential opportunities.

The data looks a little like the below:

Opportunitu ID= ........

Opportunity Name= .......

Contract Value= $12,000

Revenue Start Date= 07/09/2022

Contract Length Months= 12

In year monthly price - $1,000

In year total Revenue - $10,000

I want to be able to equally split the $12,000 across the 12 months and have $1,000 in each month starting Sept-22.

oyr Fynancial year is july to june

Is it possible in DAX to do so?

I have created a Date table that is linked to my dataset.

[ankitp]

Use the pbix file from Jihwan Kim that is almost how my data is structured

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your dataset looks like, but I tried to create a sample pbix file like below.

I hope the below can provide some ideas on how to create a solution for your dataset.

Please check the attached pbix file and the below picture.

Monthly Opp value measure: =
VAR _oppvaluetotal =
    SUM ( Opportunity[Contract Value] )
VAR _contractlengthmonth =
    SUM ( Opportunity[Contract Length Months] )
VAR _monthlyvalue =
    DIVIDE ( _oppvaluetotal, _contractlengthmonth )
VAR _revenuestartdate =
    EOMONTH ( MAX ( Opportunity[Revenue start date] ), -1 ) + 1
VAR _revenueenddate =
    EOMONTH ( MAX ( Opportunity[Revenue start date] ), _contractlengthmonth )
RETURN
    IF (
        _revenuestartdate <= MAX ( 'Calendar'[Date] )
            && _revenueenddate >= MIN ( 'Calendar'[Date] ),
        _monthlyvalue
    )

[ankitp]

hi

This is still putting the full amount in months i need it to spread over the contract period is something missing

[amitchandak]

@ankitp , refer these ways

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

For Range

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

https://amitchandak.medium.com/power-bi-power-query-vs-dax-months-between-range-df019cec823b

[danextian]

Hi @ankitp ,  Can you please post a sanitized pbix of your use case?