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
