Start Date to End date datamodel

[vincentnoot]

Hi,

I have a dataset with the Start and End date of a course, along with the total time required to finish the course.

I want to calculate a monthly commitment of time based on the amount of days between the Start date and End date.

I've considered making a relation with my Date dimension table on the Start date and calculating the count of the amount of days multiplied by (total time commitment) / DATEDIFF(Start date, End date) but this would result in confusing behavior when filtering the data on month/year.

I've also considered adding a custom column like so:

 #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Listofdays", each { Number.From([Start date])..Number.From([End date])}),

but this creates a list of every day between the two dates, resulting in 15 million rows in the table.

Is there any way to solve this problem so we can calculate the time per month without blowing up the dataset? 

Thanks!

[tamerj1]

Hi @vincentnoot 
Would you please advise what is the original DAX you've been using?

[Greg_Deckler]

@vincentnoot Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.