Revenue by Month, Start Date and End Date

Anonymous — Thu, 18 Feb 2021 02:39:43 GMT

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

Re: Revenue by Month, Start Date and End Date

Anonymous — Thu, 18 Feb 2021 21:16:34 GMT

Thanks you amitchandak just what i needed.

topic Re: Revenue by Month, Start Date and End Date in DAX Commands and Tips

Revenue by Month, Start Date and End Date

Re: Revenue by Month, Start Date and End Date

Re: Revenue by Month, Start Date and End Date