Revenue by Month, Start Date and End Date

[Anonymous]

Hi,

I have been attempting (somewhat sucessfully) to distribute revenue from hires over months.

I have a bunch of hire data with the following:

Hire Table:

Attribute	Types of Instances
Hire Type	H or L
Address Num	#######
Asset Number	####
Start Date	01/07/2020
End Date	05/09/2020
Weekly Rate	500

I also have a table called global dates, which contains every date in the finaincial year and month formated "yyyy-mm"

The below DAX formula has given me some sucess, however when I total it all, the numbers do not match the tally in the monthly figures.

RevInMonth = CALCULATE(

SUMX('Hire Table',

      (MIN('Hire Table'[End Date],LASTDATE('Global Dates'[Date])) -

      MAX('Hire Table'[Start Date], FIRSTDATE('Global Dates'[Date]))+1) * 'Hire Table'[User Amount] / 7

       ),

FILTER(

     'Hire Table',

      DATE( YEAR( 'Hire Table'[Start Date] ), MONTH( 'Hire Table'[Start Date]) , 1)

      <= FIRSTDATE( 'Global Dates'[Date])

&& (

       EOMONTH( 'Hire Table'[End Date], 0) >= LASTDATE('Global Dates'[Date])

       )

&& (

       'Hire Table'[W.O. Type] = "E"

)

)

)

Can anyone give me some advice on how to properly formulate this. Please bare in mind I need to catagorise by the type of hire and the address number (customer). Everywhere else i have looked at seems to only sumerise by month without any way of breaking down the revenue.

Many thanks in advance

[amitchandak · Accepted solution]

@Anonymous , refer these blogs if the approach can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Thanks you @amitchandak just what i needed.