Generate Data Between Start and End Date for Visualization

[MTOnet]

Hi there,

I have a problem that I have been trying to solve, but have not been abl to find a solution.  I also have not found any other related solutions that could get me where I am trying to get.

I have data in the following form.  I want to create a line graph visualization from the start date to end date, where the y-axis is the calculated percentage complete based on the number of business days between the start and end date.  So for example, if I have 25 business days in the cycle, each day will have 4% more complete than the day before. (For Saturday and Sunday, it should show the same value as Friday)

Release  Cycle   Start Date  End Date
Release  CycleA  1/13/2019   1/20/19
Release  CycleB  1/21/2019   1/31/2019
Release  CycleC  1/15/2019   2/8/2019 ` 

I already have the number of business days, but I am having issues generating the graph data.  I'm not sure if I can do this through a measure or a generated table, or date table with calculated column with values between the start and end date.  

I have slicers on the report for Release and Cycle, so it will only be one Release/Cycle at a time and I am able to get those values to use them as inputs.

Thank you for any assistance with this.

[Anonymous]

Hello.

I have almost the same problem.

I have 2 DATA TABLEs

Table1. Date | Qty | User

Table2. Start Date | End Date       |  User

01/08/2019 |      -                | User1

02/08/2019 | 10/08/2019   | User2

05/08/2019 | 18/08/2019   | User3

16/08/2019 | 22/08/2019   | User2

03/09/2019 | 10/09/2019  | User3

Also i have Calendar table.

Is it posible to generate some new table with ALL dates from table 2 to build a graph?

For example:

NewTable:

Date | User

If EndDate is null or - then it is Current Date

[Anonymous]

Not actual

Solved by merging queryes

[Greg_Deckler]

I believe what you want is a date table and a measure. The measure should get the start date and the end date for the cycle. Then it should check the MAX date from the Date table. The Date table is used as the x-axis so each day, the MAX will be the date for that "point" in the line chart. Now, first check if that date is between the start and end dates. If not, return BLANK. If so, then do your calculation and return that value.

This *should* get you what you are looking for, a line chart with your calculation that is constrained within your start and end dates. This assumes I understand what you are looking for...

[MTOnet]

Thanks for the response Greg.

What you have mentioned is basically where I have gotten to.  I have measures extracting the Start and End Dates for the selected Release and Cycle along with a date table.  On the Date Table, since I wasnt sure what dates to create it using, made the range quite large, extending into the future.  Is this what I should do, or is there a way to dynamically generate the table based on the Start and End Dates?

I will take an attempt at what you suggested and see what I can come up with.

[Greg_Deckler]

I don't believe that creating the date range dynamically will work. The reason is that you can't use a measure to return a table outside of a calculated table expression, which is only calculated at the time of data load. And even if you could, you can't use a measure in an x-axis. So, that's likely not going to work. 

You could try using CALENDARAUTO to generate your Date table and see if that works. Would possibly shrink up your date table that would change automagically as data is added/removed.

[MTOnet · Accepted solution]

Greg, thank you for your suggestions.  I was able to get this working.

Here is my solution, to hopefully help out others.

• I created measures to get my start and end dates by project.
• I then created a Date Table with a calculated column to determine which days were workdays
• I used the following post to add in holidays - https://community.powerbi.com/t5/Desktop/DATEDIFF-Working-Days/td-p/130662
• I then created another calculated column to set work days to 1 and weekends/holidays to 0
• Measure to generate the proper data

  measure =
  var MaxDate = CALCULATE(MAX(Calendar[Date]))
  var MaxBetweenStart = MaxDate>=value([Cycle Start Date]) && MaxDate<=value([Cycle End Date])
  var NumberWorkingDays = if(MaxBetweenStart,
  CALCULATE ( sum(CalculatedBurnUp[Working Day]),
  DATESBETWEEN(CalculatedBurnUp[Date],VALUE([Cycle Start Date Short]),MaxDate)),BLANK())
  return
  --Dateday
  NumberWorkingDays * (100/[Cycle Business Days])