Forum Discussion

MTOnet's avatar
MTOnet
Icon for Helper III rankHelper III
7 years ago
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. ...
Need Help
  • MTOnet's avatar
    MTOnet
    7 years ago

    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])
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

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...