How to calculate revenue for every month up to the current month

[PBI_newuser]

Hi, I have a set of data and I need to calculate the revenue and volume for every month based on the contract start and end date. 

Is there any way to create one measure to see the revenue of each month?

Below is the example of column I created for Oct-18. User wanna see the revenue for each month up to current month.

Oct-18 Revenue = 
IF('Table'[Contract End Date]<Date(2018,10,1),0,
IF('Table'[Contract Start Date]>Date(2018,10,31),0,
IF(AND('Table'[Contract Start Date]<=Date(2018,10,1),'Table'[Contract End Date]>Date(2018,10,31)),(DATEDIFF(Date(2018,10,1),Date(2018,10,31),DAY)+1)*'Table'[Net Price],
IF(AND('Table'[Contract Start Date]>Date(2018,10,1),'Table'[Contract End Date]>Date(2018,10,31)),(DATEDIFF('Table'[Contract Start Date],Date(2018,10,31),DAY)+1)*'Table'[Net Price],
IF(AND('Table'[Contract Start Date]<=Date(2018,10,1),'Table'[Contract End Date]<=Date(2018,10,31)),(DATEDIFF(Date(2018,10,1),'Table'[Contract End Date],DAY)+1)*'Table'[Net Price],
IF(AND('Table'[Contract Start Date]>=Date(2018,10,1),'Table'[Contract End Date]<=Date(2018,10,31)),(DATEDIFF('Table'[Contract Start Date],'Table'[Contract End Date],DAY)+1)*'Table'[Net Price],0))))))

Here is the sample.

[v-rzhou-msft · Accepted solution]

Hi @PBI_newuser 

Due to your data model, we couldn't use measure to get the result direcly. If you want to show the sum of each month by the group of Product, Order Doc and Conrtact number, you can use generate function to build a dax table and then build a matrix visual. 

Append Table = 
VAR _T =
    GENERATE (
        ADDCOLUMNS (
            CALENDAR (
                DATE(2018,10,01),
                MAX ( 'Table'[Contract End Date] )
            ),
            "Year", YEAR ( [Date] ),
            "Month", MONTH ( [Date] ),
            "YearMonth", FORMAT ( [Date], "YYYY-MM" )
        ),
        SUMMARIZE (
            'Table',
            'Table'[Product],
            'Table'[Order Doc.],
            'Table'[Contract Number],
            'Table'[Net Price],
            'Table'[Contract Start Date],
            'Table'[Contract End Date]
        )
    )
VAR _T2 =
    SUMMARIZE (
        FILTER ( _T, [Date] >= [Contract Start Date] && [Date] <= [Contract End Date] ),
        [Date],
        [Product],
        [Order Doc.],
        [Contract Number],
        [Net Price],
        [YearMonth],
        [Contract Start Date],
        [Contract End Date]
    )
RETURN
    _T2

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[PBI_newuser]

Hi @v-rzhou-msft , thanks for the solution.

May I know how to calculate the volume by month in the "Append Table"? 

Oct-18 Volume = 
IF('Table'[Contract End Date]<Date(2018,10,1),0,
IF('Table'[Contract Start Date]>Date(2018,10,31),0,
IF(AND('Table'[Contract Start Date]<=Date(2018,10,1),'Table'[Contract End Date]>Date(2018,10,31)),1,
IF(AND('Table'[Contract Start Date]>Date(2018,10,1),'Table'[Contract End Date]>Date(2018,10,31)),(DATEDIFF('Table'[Contract Start Date],Date(2018,10,31),DAY)+1)/(DATEDIFF(Date(2018,10,1),Date(2018,10,31),DAY)+1),
IF(AND('Table'[Contract Start Date]<=Date(2018,10,1),'Table'[Contract End Date]<=Date(2018,10,31)),(DATEDIFF(Date(2018,10,1),'Table'[Contract End Date],DAY)+1)/(DATEDIFF(Date(2018,10,1),Date(2018,10,31),DAY)+1),
IF(AND('Table'[Contract Start Date]>=Date(2018,10,1),'Table'[Contract End Date]<=Date(2018,10,31)),(DATEDIFF('Table'[Contract Start Date],'Table'[Contract End Date],DAY)+1)/(DATEDIFF(Date(2018,10,1),Date(2018,10,31),DAY)+1),0))))))

[v-rzhou-msft]

Hi @PBI_newuser 

Oct-18 Revenue = 
IF('Table'[Contract End Date]<Date(2018,10,1),0,
IF('Table'[Contract Start Date]>Date(2018,10,31),0,
IF(AND('Table'[Contract Start Date]<=Date(2018,10,1),'Table'[Contract End Date]>Date(2018,10,31)),(DATEDIFF(Date(2018,10,1),Date(2018,10,31),DAY)+1)*'Table'[Net Price],
IF(AND('Table'[Contract Start Date]>Date(2018,10,1),'Table'[Contract End Date]>Date(2018,10,31)),(DATEDIFF('Table'[Contract Start Date],Date(2018,10,31),DAY)+1)*'Table'[Net Price],
IF(AND('Table'[Contract Start Date]<=Date(2018,10,1),'Table'[Contract End Date]<=Date(2018,10,31)),(DATEDIFF(Date(2018,10,1),'Table'[Contract End Date],DAY)+1)*'Table'[Net Price],
IF(AND('Table'[Contract Start Date]>=Date(2018,10,1),'Table'[Contract End Date]<=Date(2018,10,31)),(DATEDIFF('Table'[Contract Start Date],'Table'[Contract End Date],DAY)+1)*'Table'[Net Price],0))))))

You calcualted column is to distinguish whether the date in right month (in example is oct in 2018) by if function and then return 0 if out of range, and show result days * Net Price.

In Append Table, we don't need to calculate the volume by code.  You see in our Append table there are Net Price for each date in range, we just need to add Net Price into value field in visual and select sum.

Result:

You can download the sample above and have a test.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[PBI_newuser]

Thanks @v-rzhou-msft !!! This is very helpful!

[amitchandak]

@PBI_newuser , refer this or HR blog can help

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...