cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ccj
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).

 

ccj_2-1637786803545.png 

 

 

final graph example: 

ccj_1-1637786696588.png

 

 

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
v-janeyg-msft
Community Support
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:

vjaneygmsft_0-1638258259691.png

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

 

vjaneygmsft_1-1638258273469.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,
Community Support Team _ Janey

View solution in original post

8 REPLIES 8
v-janeyg-msft
Community Support
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:

vjaneygmsft_0-1638258259691.png

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

 

vjaneygmsft_1-1638258273469.png

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,
Community Support Team _ Janey

View solution in original post

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. 

backward_progression.png

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.

v-janeyg-msft
Community Support
Community Support

Hi, @ccj 

 

Check the setting in Y axis format.: invert axis

vjaneygmsft_0-1638495327006.png

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

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.


Best Regards,
Community Support Team _ Janey

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:

ccj_3-1638564842050.png

 

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:

ccj_2-1638564821838.png

 

 

thanks for al your help!

v-janeyg-msft
Community Support
Community Support

@ccj  

 

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)

vjaneygmsft_0-1638754313890.png

 

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

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
ccj
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. 

 

thanks for your help

Greg_Deckler
Super User
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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

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

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors