Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Hi @vincentnoot
Would you please advise what is the original DAX you've been using?
@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.