Start Date to End Date - Populate/Generate values inbetween

[tonijj]

Hi, 

I've been trying to find a solution to this in the forum for quite some time now, but the Time Intelligence threads out there are a bit similar but doesnt really aim at my specific issue. 

Hope someone can help 🙂

Problem Statement: I have a Start Date, and an End Date, but I need BI to populate result inbetween those dates. 

Purpose: To be able to view Utilization of Consultants per Day/Week/Month in various reports in PowerBI. 

If we look at the picture below, I have a consultants assignment starting at 2017-01-01 and ending 2017-12-31. What I want BI to do is to:

1. Auto generate the utilization value per weekdays only (see picture 2), one row per day with the Utilization value. 

I have attached an example Excel file for reference. The file can be found here.

Of course I can make a huge table in Excel, but its just gotta be possible to do it with some DAX magic right? 🙂 

Picture 1.

 

Picture 2.

[Datatouille · Accepted solution]

Hi,

You need to use the Query Editor and the List.Dates function (M language).

You will find here my solution: https://1drv.ms/f/s!Ag8khmd3N7ExgQjuCxFseRnECYoh 

[andy_tolle]

Sir,

Is there a similar function for numbers? So, similar scenario, only no dates, but in stead a startnumber and endnumber. Does such function exist?

[tonijj]

Hi,

So...wow!! I would never been able to figure that one out no matter how much I wouldve googled/read threads, a HUGE thank you for a superb solution!!!

I might as well ask as a quick followup question then, that has to do with the new Utilization. If we look at the picture below, it displays the individual items correctly, as it is an Average. 

Question: How can I keep the Utilization (preferrably, but not necessary, to percent), BUT, making PowerBI add the two numbers to a total?

Since the report refers to being a utilization report of Consultants, in this case it would mean that John Doe is actually Utilizing 80% of his time (A + B ).

I understand that the total wont be "80" as long as I have it as an "Average", Im just asking how I should've set it up instead. I have tried several non-working solutions such as Count and divide it by the sum, but, Im just not getting there 😞

Again, a big thank you! Please note that helpfulness as this really makes a difference for us still in an early learning curve, and its highly appreciated! 

[Datatouille]

Hi @tonijj,

Please accept my 1st answer as your solution and create a separate post for your 2nd need. 

This will help people find the most relevant answers.

@parry2k 's suggestion looks good 🙂

[Anonymous]

Hi

Fantastic post!

Just need help with this error. Can't work out why it won't accept my format.

Any ideas?

Thanks

Martin

[tonijj]

I'd its a regional setting you need to change? The solution I got here was based on having number format in EU format, if you have it in US maybe thats the issue?

[tonijj]

OK will take care of that now!

Understand that I should probably create a new post, but after a long day of Excel and DAX Im not sure I got it in me 😕

But, the way I see it, it shouldnt be using Average at all, but rather something like:

Sum of Utilization / Total SUM of days

I think, but hey... its soon time to give up for today

[parry2k]

To your 2nd question, you need to create a summary table with average and you can use that for sum.