cancel
Showing results for
Did you mean:
Regular Visitor

## Calculating days between, then total duration of days

Hi, I have a report from a SQL database that I need to pull dates from on the progression between phases for each project. Each project has multiple phases, and multiple action dates within the phase. Ultimately, I need to find the progression of each project through the phases in # of days (see examples from excel).

final graph example:

To do this I need the first date at a certain phase (PHASE_START), then the count of days between that phase’s start date and the next phase’s start date (or start date to last action date if it's the last phase in a project). Then, these counts need to be added together to show the progression of the project through each phase from 0 to the number of days from the very start to the latest action date of all the phases in that project.

The PHASE_START is fairly easy to get, but I’m getting stuck on counting the days between the start dates of different phases as there are multiple entries for each combination of project, phase, and action date. Any help would be greatly appreciated, thank you!!

1 ACCEPTED SOLUTION
Community Support

Hi, @ccj

According to your description, I create a sample to meet your needs.

First you need to create a distinct table to display phase and start column, I also create a date table for sort.

Then you need to create a measure to calculate the date interval.

Note: Put the fields of different tables in the line chart will report errors, but they will be displayed normally after putting measure in values.

Like this:

``````Table 2 =
UNION (
SUMMARIZE (
Table1,
Table1[PHASE],
"date",
MINX (
FILTER ( ALL ( Table1 ), [PHASE] = EARLIER ( Table1[PHASE] ) ),
[ACTUIN_DATE]
)
),
ROW ( "PHARE", "start", "date", DATE ( 2021, 1, 1 ) )
)
``````

``````Measure =
VAR filtertable =
FILTER (
ALL ( Table1 ),
[PROJECT] = SELECTEDVALUE ( Table1[PROJECT] )
&& [PHASE] = SELECTEDVALUE ( 'Table 2'[PHASE] )
)
VAR mindate =
MINX ( filtertable, [ACTUIN_DATE] )
VAR maxdate1 =
MINX (
FILTER (
ALL ( Table1 ),
[PROJECT] = SELECTEDVALUE ( Table1[PROJECT] )
&& [PHASE_START] > mindate
),
[PHASE_START]
)
VAR maxdate2 =
IF ( ISBLANK ( maxdate1 ), MAXX ( filtertable, [ACTUIN_DATE] ), maxdate1 )
RETURN
DATEDIFF ( mindate, maxdate2, DAY ) + 0
``````

Best Regards,
Community Support Team _ Janey

8 REPLIES 8
Community Support

Hi, @ccj

According to your description, I create a sample to meet your needs.

First you need to create a distinct table to display phase and start column, I also create a date table for sort.

Then you need to create a measure to calculate the date interval.

Note: Put the fields of different tables in the line chart will report errors, but they will be displayed normally after putting measure in values.

Like this:

``````Table 2 =
UNION (
SUMMARIZE (
Table1,
Table1[PHASE],
"date",
MINX (
FILTER ( ALL ( Table1 ), [PHASE] = EARLIER ( Table1[PHASE] ) ),
[ACTUIN_DATE]
)
),
ROW ( "PHARE", "start", "date", DATE ( 2021, 1, 1 ) )
)
``````

``````Measure =
VAR filtertable =
FILTER (
ALL ( Table1 ),
[PROJECT] = SELECTEDVALUE ( Table1[PROJECT] )
&& [PHASE] = SELECTEDVALUE ( 'Table 2'[PHASE] )
)
VAR mindate =
MINX ( filtertable, [ACTUIN_DATE] )
VAR maxdate1 =
MINX (
FILTER (
ALL ( Table1 ),
[PROJECT] = SELECTEDVALUE ( Table1[PROJECT] )
&& [PHASE_START] > mindate
),
[PHASE_START]
)
VAR maxdate2 =
IF ( ISBLANK ( maxdate1 ), MAXX ( filtertable, [ACTUIN_DATE] ), maxdate1 )
RETURN
DATEDIFF ( mindate, maxdate2, DAY ) + 0
``````

Best Regards,
Community Support Team _ Janey

Regular Visitor

hi @v-janeyg-msft, this is exaclty what i was looking for!!! thank you for providing the sample, really helped.

I followed your directions and modifed the sample formula to my data, and i'm running into one big issue - the numbers on my chart are counting backwards.

the line progression is correct, the x-axis is in the correct order, the difference between each point is correct, but it counts from 64 to 1 when it should be 1 to 64. I have the y-axis inverted to show the line having the updwards progression, but ideally the y-axis should start at 0 and increase, but with this line. Is there a way i could modify the measure formula to count correctly?

When i put the measure in the table, it seems to be counting backwards starting frome the most recent date, then on and on to the first date giving the start date the highest value which is incorrect.

Community Support

Hi, @ccj

Check the setting in Y axis format.: invert axis

If the result is incorrect, please provide your code and related data, otherwise it is difficult for me to judge.

Best Regards,
Community Support Team _ Janey

Regular Visitor

Hi @v-janeyg-msft, i have the y-axis inverted on purpose. the line is correct when inverted, but the values count backwards which is wrong. When the y-axis isn't inverted it looks like this:

this line is wrong and still counts backwards. i want it to look like the graph below, but the data points should count up from 1 to 64:

Community Support

I want to help you..But you just provide two screenshots, it's useless... I don’t even know what data you put on the X axis.

If it’s just the wrong order, you can sort it. I also used date column to sort the newly created table in the sample.(if you know sort by column and carefully check my sample)

If the result is incorrect, please provide your code and related data, otherwise it is difficult for me to judge.

Best Regards,
Community Support Team _ Janey

Super User

Hi,

Create a simple table visual and drag Project and Phase to the row labels.  Write these measures

First action date = min(data[action_date])

Last action date = max(data[action_date])

Duration = 1*([Last action date]-[First action date])

Format duration as a whole number.

Switch the vidual to a line visual.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

@Greg_Deckler thank you for the prompt response and forwarding me to the MTBF article! It seems very similar to what i'm doing. When following the MTBF process in my data, however, i'm having an issue with the end date (RepairCompleted in the article) as my data has a start date but no end date. That needs to reference a different phase start to essentially get that value (as the end date is not the last date listed for that phase).

I was able to use that formula format you pasted in the previous reply, but it returns "12/30/1899" in every row. If i change the [Value] reference, then i get an issue that text can't be compared with number values.

Super User

@ccj Seems like you could use an MTBF approach. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

#### Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.