Forum Discussion

MTOnet's avatar
MTOnet
Helper 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])
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

Not actual

Solved by merging queryes