Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Help with DATEDIFF

That was my problem.

 

I have a table with projects where one of the columns indicates in which phase this project is.

I also have columns indicating each phase of the project. Where I put the date when the project goes into that phase.

 

I would like to create a measure using DATEDIFF(Date1;Today();Day) to see how long does each project are in the phase.

 

But how do I make Date1 be the column corresponding to the phase the project is in?

 

ProjectStageIdeaBusiness CaseDevelopmentLaunch
P1Launch01/02/201901/03/201901/04/201901/05/2019
P2Idea01/02/2019   
P3Business Case01/02/201901/03/2019  
P4Development01/02/201901/03/201901/04/2019 
P5Launch01/02/201901/03/2019 01/05/2019
P6Launch01/02/2019 01/04/201901/05/2019
P7Development01/02/2019 01/04/2019 
P8Launch01/02/2019  01/05/2019

 

Example: Today is 05/06/2019

 

If I select P2; I want to see that P2 is on the Idea phase for 124 days.

 

if I select P3; I want to see that P3 is on the Business Case phase for 96 days.

 

Any Ideas?

 

1 ACCEPTED SOLUTION

hi, @Anonymous 

Just try this formula to create a column:

NewDaysInStage = 
IF (
    Table1[LatestStage] = Table1[Stage],
    DATEDIFF ( Table1[StageDate], TODAY () , DAY ),
    DATEDIFF (
        Table1[StageDate],
        CALCULATE (
            MIN ( Table1[StageDate] ),
            FILTER (
                Table1,
                Table1[Project] = EARLIER ( Table1[Project] )
                    && Table1[StageDate] > EARLIER ( Table1[StageDate] )
            )
        ),
        DAY
    )
)

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
tarunsingla
Solution Sage
Solution Sage

How does your data model look like? Is it 3 columns (Project Id, Stage, Date) or is it multiple columns (1 for date per stage)? Can you post a sample of your source data?

Also, take a look at the unpivoting feature in Power BI to convert columns to rows.

https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks#pivot-columns

https://radacad.com/pivot-and-unpivot-with-power-bi

 

Anonymous
Not applicable

Thanks for answering me @tarunsingla 

 

The database is the same as this table, but with more columns. I have one line per project, which over time I update the status.

 

But I still can not see how pivoting the table can help me .

See this unpivot example pbix file to understand how unpivoting can help you here.

Unpivot Example

 

Regards,

Tarun

Anonymous
Not applicable

Tnks for the help @tarunsingla 

 

You were right about unpivoting. But I seeing another problem now.

 

Take P4 in your example.

Idea - 02/01/2019

Bussiness Case - 03/01/2019

Development - 04/01/2019

 
DaysInStage = DATEDIFF(Table1[StageDate]; TODAY(); DAY) shows that P4 is 156 days in Idea Stage instead of only 1 day.
 
Anyway to stop the day count when I change de stage?
 
 

 

 

hi, @Anonymous 

What is your expected output of P4?

Why it is only 1 day? Can you show the expected output in different scenario?

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-lili6-msft 

 

Using P4 as an example.

 

Today() = 11-jun-19.

I would like to see that P4 is in Development Stage, since 01-apr-19. So, P4 is in Development for 71 days. (Today()) - (01-apr-19)

But, P4 took 28 days to leave the Idea Stage. (01-mar-19) - (01-feb-19)

And 31 days to leave the Business Case Stage. (01-apr-19) - (01-mar-19)

 

Best Regards,

 

hi, @Anonymous 

Just try this formula to create a column:

NewDaysInStage = 
IF (
    Table1[LatestStage] = Table1[Stage],
    DATEDIFF ( Table1[StageDate], TODAY () , DAY ),
    DATEDIFF (
        Table1[StageDate],
        CALCULATE (
            MIN ( Table1[StageDate] ),
            FILTER (
                Table1,
                Table1[Project] = EARLIER ( Table1[Project] )
                    && Table1[StageDate] > EARLIER ( Table1[StageDate] )
            )
        ),
        DAY
    )
)

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.