Comparing Dates (Slippage) vs current Go Live Date Month

[romovaro]

HI all,

I have the table below showing CUID Number (=project number), GLD (Go Live Date) and the GLD that had this project in prev months.

What I am trying to achieve is to track the changes monthly compared to GLD

Like the Excel formula (CellA1=CellB1) but for all the different months vs GLD...is there any way to automatize this?

or Maybe a formula comparing current month vs pre month and current month vs 2 month ago and current month vs 3 months ago...? (Last column it's always going to show current month)

See below some highlighted changes.

For example, ULLCPH01 had 1 Change. Same as ULLCNL02.

XROXDK01 had 2 changes.

Another think I would like to achieve is to show if the slippage (change) was done +months prev GLD or not.

Thanks for any tip,

[jgeddes · Accepted solution]

Thanks for attaching the report.

Change the period column to type text then this measure works

Previous Period Changed? =

var _currentPeriod =

SELECTEDVALUE(RPT10[Period])

var _currentGLD =

FORMAT(SELECTEDVALUE(RPT10[GLD]), "YYYY-MM")

var _prevPeriod =

FORMAT(EOMONTH(_currentPeriod,-1), "YYYY-MM")

var _prevGLD =

FORMAT(LOOKUPVALUE(RPT10[GLD],RPT10[Period Column],_prevPeriod, RPT10[CUID], SELECTEDVALUE(RPT10[CUID])), "YYYY-MM")

Return

IF(

    // test for beginning for date list

    ISBLANK(_prevPeriod) || _prevPeriod = "",

    "No Change",

    IF(

        _currentGLD = _prevGLD,

        "No Change",

        "CHANGED"

    )

)

[jgeddes · Accepted solution]

CALCULATE(
MIN(RPT10[GLD]),
ALLEXCEPT(RPT10, RPT[CUID])
)

should pull the earliest date for each CUID

[romovaro]

Thanks. It works.

[jgeddes]

I am not sure that I 100% understand the outcome you are looking for but

I created a small dataset based on your example as follows

Using that data I made the following measure

Previous Period Changed? =

var _currentPeriod =

SELECTEDVALUE(gldTable[Period])

var _currentGLD =

FORMAT(SELECTEDVALUE(gldTable[GLD]), "YYYY-0M")

var _prevPeriod =

FORMAT(DATEADD(gldTable[Period],-1,MONTH), "YYYY-0M")

var _prevGLD =

CALCULATE(values(gldTable[GLD]),gldTable[Period Column]=_prevPeriod)

Return

IF(

    // test for beginning for date list

    ISBLANK(_prevPeriod) || _prevPeriod = "",

    "No Change",

    IF(

        _currentGLD = _prevGLD,

        "No Change",

        "CHANGED"

    )

)

and ended up with this

Hope this helps.

[romovaro]

Thanks jgeddes,

Thanks for your answer. That looks good. I see that you combined all dates column in one column called period.

The thing is that every month I will get the excel file like the one below:

One column with dates for every month. 

Tried different ways to Merge/Group to move all dates in one column based on CUID and create your "period column" but it seems I am unable to do it.

[jgeddes]

In the format you have you will need to unpivot the columns in Power Query and then you will be able to add the Period column. The period column I created was just done with DAX. FORMAT(gldTable[Period[, "YYYY-MM")

[romovaro]

hi @jgeddes,

Quick question regarding this report

As discussed previously, my data source is the Excel below.

Report is monthly and "Actual GOLIVE" column always uses the most updated go Live Date in the file. (see example below). That means that I cannot use the "Actual Go live Data columns" as the "Original Go Live Date"

Is it possible to create a measure that takes the earliest date (that we can use as the Original Go Live Date) and compare with the changes?

Tried

Earliest GLD = MIN(RPT10[GLD]) but it's showing the same data as the month date.

For example first Row (CUID 380305KR01) 

Original Go Live Date should be: Jun-2022

Same date for CUID 380305MX01

thanks

@jgeddes 

[jgeddes · Accepted solution]

CALCULATE(
MIN(RPT10[GLD]),
ALLEXCEPT(RPT10, RPT[CUID])
)

should pull the earliest date for each CUID

[romovaro]

Thanks

Always a "Life saver". it works. Thanks.

I was tring to update your formula to compare the Earliest GLD and the "Actual Go Live" date 

but it seems, is not really showing the right results.

The idea, as previous comments, is to show:

- No Change

- Changed

- Changed + 3

[romovaro]

HI jgeddes,

if I use the formula:

Inital GLD Changed = IF(RPT10[GLD] = [Earliest GLD], "No Change", "Changed")

It shows correctly if changed or not (But I would like to keep the changed and Changed +3 filter) . I created a new column shwoing Months diff

Months_between = DATEDIFF([Earliest GLD],RPT10[GLD],MONTH) to get the diff and then create the filter based in numbers but it's showing wrong calculation...

@jgeddes 

[jgeddes · Accepted solution]

You should be able to change the Initial GLD Changed measure to

Inital GLD Changed = 
SWITCH(
TRUE(),
RPT10[GLD] = [Earliest GLD], "No Change",
DATEDIFF([Earliest GLD], RPT10[GLD], MONTH) >=3, "Changed +3",
DATEDIFF([Earliest GLD], RPT10[GLD], MONTH) <3, "Changed"

)

[romovaro]

Thanks for the Help. I unpivoted the columns in Power Query I created the columns like your screenshot.

but is not working. 

It says something about Dax comparison operations do not support comparing values of type Date with Text values but I updated all the fileds involved to Date format.

Attached a report in case it helps.

[jgeddes · Accepted solution]

Thanks for attaching the report.

Change the period column to type text then this measure works

Previous Period Changed? =

var _currentPeriod =

SELECTEDVALUE(RPT10[Period])

var _currentGLD =

FORMAT(SELECTEDVALUE(RPT10[GLD]), "YYYY-MM")

var _prevPeriod =

FORMAT(EOMONTH(_currentPeriod,-1), "YYYY-MM")

var _prevGLD =

FORMAT(LOOKUPVALUE(RPT10[GLD],RPT10[Period Column],_prevPeriod, RPT10[CUID], SELECTEDVALUE(RPT10[CUID])), "YYYY-MM")

Return

IF(

    // test for beginning for date list

    ISBLANK(_prevPeriod) || _prevPeriod = "",

    "No Change",

    IF(

        _currentGLD = _prevGLD,

        "No Change",

        "CHANGED"

    )

)

[romovaro]

HI jgeddes,

One last thing. I have been asked if I can highlight the Changes +3months. Meaning that The change from the previous month has been equal to or greater than 3 months. It's possible?

And one question about the measure. I am trying to use the measure also as a filter to show only the ones that "Changed". But it seems filter is not showing all of the changes when I select more than one month...any idea why?

Filter applied: (as Example, June shows 33.461$.

But when I select only One month (example June) , then it shows all of them.

Thanks

[jgeddes]

For this case we will need to change from a measure to a calculated column.
In your RPT10 table add the calculated column;

Changed GLD? =

var _currentPeriod =

RPT10[Period]

var _currentGLD =

FORMAT(RPT10[GLD], "YYYY-MM")

var _prevPeriod =

FORMAT(EOMONTH(_currentPeriod,-1), "YYYY-MM")

var _prevGLD =

FORMAT(LOOKUPVALUE(RPT10[GLD],RPT10[Period Column],_prevPeriod, RPT10[CUID], RPT10[CUID]), "YYYY-MM")

var _gldDifference =

abs(DATEDIFF(DATEVALUE(_currentGLD),IF(OR(ISBLANK(_prevGLD),_prevGLD=""),DATEVALUE(_currentGLD),DATEVALUE(_prevGLD)),MONTH))

Return

IF(

    // test for beginning for date list

    ISBLANK(_prevPeriod) || _prevPeriod = "",

    "No Change",

    IF(

        _gldDifference = 0,

        "No Change",

        IF(

            _gldDifference < 3,

            "Changed",

            "Changed+3"

        )

    )        

)

You now use the calculated column in the matrix visual instead of the measure. You can then use that column as a slicer to choose which items you want to see.

[romovaro]

Thanks jgeddes. It works perfectly.

Happy weekend

I have another question in the forum in case you want to have a look.

https://community.powerbi.com/t5/Desktop/Average-in-Dates-with-delivered-projects/m-p/2772236#M96325...