Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
tonijj
Helper IV
Helper IV

Start Date to End Date - Populate/Generate values inbetween

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.

 bi issue 1.png

 

Picture 2.

bi issue 2.png

1 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 

View solution in original post

8 REPLIES 8

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 

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?

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! 

 

 

bi issue 3.png

 

 

bi issue 3.png

 

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
Not applicable

Hi

 

Fantastic post!

 

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

 

Any ideas?

 

Thanks


Martin

 

FormatProblemDateFormatDuration.PNG

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?

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.