Forum Discussion
Helper IVStart 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.
Picture 2.
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
8 Replies
- Datatouille
Solution Sage
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
tonijjHi,
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!
- parry2k
Super User
To your 2nd question, you need to create a summary table with average and you can use that for sum.
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?
