cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBI_newuser
Post Prodigy
Post Prodigy

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

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.

PBI_newuser_0-1624003863328.png

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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.

1.png

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. 

View solution in original post

5 REPLIES 5
RicoZhou
Community Support
Community Support

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.

1.png

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. 

View solution in original post

Hi @RicoZhou , 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))))))

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.

1.png

Result:

2.png

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. 

Thanks @RicoZhou !!! This is very helpful!

amitchandak
Super User IV
Super User IV

@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...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors