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