Forum Discussion
Generate Data Between Start and End Date for Visualization
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])
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...
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.
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.
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])