cancel
Showing results for
Did you mean:  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. 1 ACCEPTED SOLUTION  Community Support

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

5 REPLIES 5  Community Support

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 (
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.  Post Prodigy

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))))))`  Community Support

``````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: 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.  Post Prodigy

Thanks @RicoZhou !!! This is very helpful!  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...

Proud to be a Super User! Announcements #### Welcome to the User Group Public Preview  