Project status duration

[lamacl01]

Hi all,

I would use some help with query related to projects overview. I have a list of projects IDs (Items), their status (Status) and date when project's status has been updated (Update Date). Each update is in a separate row.

Items	Status	Update Date
1	A	14/05/2018
1	B	14/06/2018
1	C	17/07/2018
1	D	12/09/2018
1	E	01/12/2018
1	F	01/03/2019
1	G	01/06/2019
2	A	14/01/2018
2	B	14/02/2018
2	C	14/03/2018
2	D	14/04/2018
2	E	14/05/2018
2	F	14/06/2018
2	G	14/07/2018
3	A	20/01/2017
3	B	20/03/2017
3	C	20/05/2017
3	D	20/07/2017
3	E	20/09/2017
…	…	…

What I need it so create a table where every project 's status will have reflected month or months when this status was current. Like in table here

Item	Month	Status
1	Jan-18
1	Feb-18
1	Mar-18
1	Apr-18
1	May-18	A
1	Jun-18	B
1	Jul-18	C
1	Aug-18	C
1	Sep-18	D
1	Oct-18	D
1	Nov-18	D
1	Dec-18	E
1	Jan-19	E
1	Feb-19	E
1	Mar-19	F
1	Apr-19	F
1	May-19	F
1	Jun-19	G
1	Jul-19
1	Aug-19

So I would be able to identify how many months each status of project lasted.

Is there anyone who can help me with this?

Many thanks

Lukas

[sdjensen]

Hi @lamacl01,

You could try with this DAX measure:

Status (MonthEnd) = 
VAR MaxUpdateDate = CALCULATE( MAX( Table1[Update Date] ); ALL( Period ) )

RETURN
IF( 
    MIN( Period[Date] ) <= MaxUpdateDate;
    CALCULATE(
        MAX( Table1[Status] );
        FILTER(
            ALL( Period );
            Period[Date] <= MAX( Period[Date] )
        )
    )
)

Besides your data for illustration I have added a calendar table (called period) and a table with all the items to my model and created a relationship between the tables.

From the above I can get this result:

[lamacl01]

@sdjensen Hi,

many thanks for your help. Unfortunatelly I wasn't able to use this function correctly for my data. Whould you be able to double check your formula? In attached screen there is a result I am receiving. If you compare right table with source data and results on the left table, there is something wrong.

my function:

Status (MonthEnd) = 
VAR MaxUpdateDate = CALCULATE( MAX( Sheet1[Date of change] ), ALL( 'Calendar' ) )

RETURN
IF( 
    MIN( 'Calendar'[Date] ) <= MaxUpdateDate,
    CALCULATE(
        MAX( Sheet1[Attribute.1] ),
        FILTER(
            ALL( 'Calendar'),
            'Calendar'[Date] <= MAX( 'Calendar'[Date] )
        )
    )
)

[sdjensen]

Hi @lamacl01,

Your formula seems okay.

Can you verify that your Calendar table includes ALL dates from the first day of the year of your lowest date in [Date of Change] to the last day of the year of the maximum date in [Date of Change] ?

Can you verify that you have created a relationship between your Sheet1 table and your calendar table?

I noticed that the ID column in Sheet1 seems to be formated as a number, hence when adding it to visuals PBI will by default think this is a number to an summarized, which would never be the case. It should not affect the result of your formula, but you should format it as a text in the Query Editor.

[lamacl01]

Hi @sdjensen,

I have checked everything you are mentioning, I have a calendar table dated from 01/01/2016 and there is also connection between calendar and date. I have also changed data type of Id to Text.

Hovewer still my results are not the ones I would like to get.

If you have a look at left table results for ID 10. There are only four rows with months, but I would like to get a full lifetime of project overview something like this

 

Is this something you would be able to help me with?

Regards.

Lukas

[lamacl01]

Hi @sdjensen,

I have tried your suggestion and created a column StatusID in my source file using "StatusID" column in my REF table.

StatusID =
CALCULATE (
    VALUES ( 'REF table'[StatusID] ),
    Sheet1[Attribute.1] = RELATED ( 'REF table'[Status] )
)

which I have used for measure Status (MonthEnsd)

Status (MonthEnsd) = 
VAR MaxUpdateDate =
    CALCULATE ( MAX ( Sheet1[Date of change] ), ALL ('Calendar') )
VAR MaxStatusId =
    IF (
        MIN ( 'Calendar'[Date] ) <= MaxUpdateDate,
        CALCULATE (
            MAX ( Sheet1[StatusID] ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
        )
    )
RETURN
    CALCULATE ( VALUES ( Sheet1[StatusID] ), MaxStatusId = Sheet1[StatusID] )

there is a conection between my REF table and source table with status description

unfortunatelly I am still not receiving correct output.

Whould you know what might be a problem here?

Many thanks

Regards.

Lukas

[sdjensen]

Hi @lamacl01,

I had a look at your file.

You have to Status' with the same value in StatusID in the Status table. This is causing your problem with the formula not working.

Also you should add a column in your Calendar table to sort your "Month & Year" column. I created a calculated column with the following DAX (remember to set the column to data type: Whole Number)

YearMonthSort = YEAR('Calendar'[Date]) & RIGHT("0" & MONTH('Calendar'[Date]); 2)

Then select your "Month & Year" Column and on the Modeling Tab you select "Sort by Column" and then your new calculated column.

[sdjensen]

Hi @lamacl01,

It's strange that it's working in my model, but not yours. Did you change the relationship to your date table as I described in my last post?

Would you be able to share your .pbix file with me, so I can have a look at it? If you don't want to share it to the public, you can send me a PM with a download link to it.

[sdjensen · Accepted solution]

Hi @lamacl01,

You get a wrong result because your values in the Status column will not return the correct result if you pick the maximum value, so we need to add some kind of statusID column, that can be used to sort the status's in the correct order.

As you can see in the screenshot below, I have added a Status table that also includes a statusId column. I have also calculated a new statusID column in my Table1 that I will need for the calculation. It's important that the relationship between the tables is set to 'Table1'[Status] --> 'Status'[Status] and not between the ID columns, hence you need to the relationship to calculate the statusID in Table1

I have calculated the 'Table1'[StatusID] with this formula: (edit: wrote Status instead of StatusID)

StatusID = 
CALCULATE( 
    VALUES( 'Status'[StatusID] ); 
    Table1[Status] = RELATED( 'Status'[Status] ) 
)

Then we need to change the formula for the "Status (MonthEnd)" Measure and first calculate the Maximun StatusID used on an item by the end of a given month and then use this value to calculate the Status that you want to see. My final measure looks like this now:

Status (MonthEnd) = 
VAR MaxUpdateDate = CALCULATE( MAX( Table1[Update Date] ); ALL( Period ) )
VAR MaxStatusId =
IF( 
    MIN( Period[Date] ) <= MaxUpdateDate;
    CALCULATE(
        MAX( Table1[StatusID] );
        FILTER(
            ALL( Period );
            Period[Date] <= MAX( Period[Date] )
        )
    )
)
RETURN
CALCULATE(
    VALUES( 'Status'[Status] );
    MaxStatusId = 'Status'[StatusID] 
)

And this is the result I get with your data:

Edit: I noticed something with your relationship to your calendar table - you have set Cross Filter Direction to "Both" - this should always be single unless you really need "Both".