Days Accumulated Since Start of Month

[Anonymous]

Hi Everyone

I am trying to show how many days of a rental period have been accrued in the 'current' month. For example, if an ongoing rental period is on 56 days, I need to know how many days of that period have been accrued in the 'current' month.

My data set has the rental 'start date' and 'end date' so working out total rental length with Datediff is easy enough. Where I am struggling is with days accrued in 'current' month.

In my mind I thought I needed a column that worked out if a rental was live in the 'current' month so I created a column that does this. I then thought I would use this column along with another piece of DAX so I tried this: Days Sold This Month = DATEDIFF(STARTOFMONTH(),Hire_Data[Off Hire Date],DAY) but sadly it didn't.

Does anyone have any help/tips/a solution to this?

Many thanks

James

[kentyler]

STARTOFMONTH() needs a date as an argument 

=STARTOFMONTH(DateTime[DateKey])

[Anonymous]

Hi Ken

Thanks for looking at this.

If i did that and used it in the DAX like this, =datediff(startofmonth(rental info[start date]),rental info[end date]),Day) I get an artificially increased rental length. 

For example, a rental starting on 15/04/20 and ending 12/05/20 has a 28 day rental length but the above DAX returns a 41 day rental. What I'm aiming for is it to show a 12 day rental length, signifying 12 of the 28 days rental were accrued in May.

Hope this helps clarify my issue and thanks again.

James

[mahoneypat]

Days Sold This Month = DATEDIFF(STARTOFMONTH(),Hire_Data[Off Hire Date],DAY) 

Assuming you have a Date table and you are using a column from that in your visual, you could use an approach like this:

Hired Days =
VAR mindate =
MIN ( Date[Date] ) //get min and max dates from you visual that uses a column from Date table
VAR maxdate =
MAX ( Date[Date] )
VAR result =
SUMX (
Hire_Data, // iterate over each row of your main fact table

VAR adjmindate =
IF ( Hire_Data[On Hire Date] < mindate, mindate, Hire_Data[On Hire Date] ) //figure out which date to use as min
VAR adjmaxdate =
IF ( Hire_Data[Off Hire Date] > maxdate, maxdate, Hire_Data[Off Hire Date] ) //figure out which date to use as max
RETURN
DATEDIFF ( adjmindate, adjmaxdate, DAY ) //return difference in days so it will be summed with all the other rows
)
RETURN
result

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi Pat

Thanks for your post. It has returned "486962" for all rows. 

I have checked the relationship between the fact table and the calendar look up and it is correct.

Just to confirm, I need to get a calculated column that tells me how many days of a rental period have been accrued in the 'current month'.

Some rental periods will have started in a previous month so the days accrued in the previous month(s) need to be excluded. Some rentals will not have accrued any days in the 'current month' because they started and ended in previous months and so should show '0' 

Many thanks again

James