Average in Dates with delivered projects

[romovaro]

HI all

I have a table showing:

CID = Number assigned to a Customer (all entities)

CUID = Unique entity number

Roll Call date and Actual Go Live Date (Actual GLD)

Now I need to calculate (As CID level) the AVERAGE between the first Roll call date of the customer and the first Actual GLD.

For example, CID 1000121

The first (oldest Roll Call date) is 01/10/2021

The first (oldest Actual GLD) is 01/04/2022

Average in month for this customer (assuming all CUIDs were delivered) should be: 6 Months

The thing is that I only want to include the customers (CID) with all entities (CUID) done/delivered or with date 01/01/2050 (Actual GLD before today or if Date is 01/01/2050)

• I guess first I will need to have a filter to get ONLY the customers (CID) with all CUIDs delivered/or 01/01/2050
• Once I have the list, a formula to get the average in months between the first (oldest) Roll-Call date and the first (oldest) Actual GLD.

Currently I have the formula for CUIDs (entity level) to see if projects are delivered /Scheduled/ 01/01/2050….and it’s working fine.

Sched-Unsch = Switch( true() ,

[Ops Forecast & Actuals] <= eomonth(Today(),-1), "Done",

eomonth([Ops Forecast & Actuals],0) = eomonth(Today(),0), "Scheduled",

[Ops Forecast & Actuals] > eomonth(Today(),0) && [Ops Forecast & Actuals] <date(2049,12,1) , "Scheduled",

[Ops Forecast & Actuals] = date(3000,12,1) , "Unscheduled",

"Undefined-2050"

)

But the formula does not filter only the CIDs with all the CUIDs delivered. It shows all the CIUDs delivered but also of the clients that still have CUID pending. I want to avoid that.

Any help will be appreciated

Thanks

[jgeddes · Accepted solution]

Based on your example I created the dataset
CUIDtable

CIDCUIDSigned DateGLD

WEBF	WEBFCH01	11/1/2019	2/1/2020
WEBF	WEBFCH02	11/1/2019	3/1/2020
WEBF	WEBFCH03	11/1/2019	4/1/2020
WEBF	WEBFCH04	1/1/2020	4/1/2020
WEBF	WEBFCH05	11/1/2019	1/1/2050
WEBF	WEBFCH06	11/1/2019	3/1/2020
WEBF	WEBFCH07	11/1/2019	11/1/2025
FSTH	FSTH01PH01	11/1/2019	2/1/2020
FSTH	FSTH01PH02	11/1/2019	3/1/2020
FSTH	FSTH01PH03	11/1/2019	4/1/2020
FSTH	FSTH01PH04	1/1/2020	4/1/2020
FSTH	FSTH01PH05	11/1/2019	1/1/2050
FSTH	FSTH01PH06	11/1/2019	3/1/2020
JKMN	JKMN001	10/1/2019	4/1/2021
JKMN	JKMN002	10/1/2019	5/1/2021
JKMN	JKMN003	11/1/2020	7/1/2021

I created measures

Earliest Signed Date =

MIN(CUIDtable[Signed Date])

GLD from Earliest Date =

var _GLDtest =

//get max GLD that is not 1/1/2050

CALCULATE(

    MAX(CUIDtable[GLD]),

    ALLSELECTED(CUIDtable),

    FILTER(CUIDtable, CUIDtable[GLD] < DATE(2050,1,1))

)

var _GLDdate =

// if GLD is later than yesterday (i.e. not yet live) return the earliest GLD

IF(

    _GLDtest >= TODAY(),    

    BLANK(),

    CALCULATE(

        min(CUIDtable[GLD]),

        FILTER(CUIDtable,CUIDtable[Signed Date] = [Earliest Signed Date])

    )

)

Return

_GLDdate

Months Difference =

DATEDIFF([Earliest Signed Date], [GLD from Earliest Date], Month)

Diff Average =

var _table  =

SUMMARIZE(

    CUIDtable,

    CUIDtable[CID],

    "_months", DATEDIFF([Earliest Signed Date], [GLD from Earliest Date], Month)

)

Return

AVERAGEX(

    _table,

    [_months]

)

and ended up with the result

This should get you pointed in the right direction.

[jgeddes · Accepted solution]

I think the issue is trying to filter with measures, which is not possible.
If you create a calculated column for the CUID size you will then be able to filter by it. 
I added the following column to 'Weekly Slippage'

CUID Size =

var _count =

CALCULATE(

    COUNT('Weekly Slippage'[CUID]),

    ALLEXCEPT('Weekly Slippage','Weekly Slippage'[CID])

)

var _result =

IF(

    _count <=5, "1.Up to 5 CUID",

    IF(

        _count <= 10,

        "2.Between 5 to 10 CUID",

        IF(

            _count <= 20,

            "3.Between 10 to 20 CUID",

            IF(_count <= 30,

            "4.Between 20 to 30 CUID",

            "5.More than 30"

            )

        )

    )

)

Return

_result

Similarly you cannot use the Earliest Signed Date as a filter, but you could put the signed date column into the filters field (either for a specific filter or page or all pages) and then select the signed date you are looking for.

[jgeddes · Accepted solution]

Based on your example I created the dataset
CUIDtable

CIDCUIDSigned DateGLD

WEBF	WEBFCH01	11/1/2019	2/1/2020
WEBF	WEBFCH02	11/1/2019	3/1/2020
WEBF	WEBFCH03	11/1/2019	4/1/2020
WEBF	WEBFCH04	1/1/2020	4/1/2020
WEBF	WEBFCH05	11/1/2019	1/1/2050
WEBF	WEBFCH06	11/1/2019	3/1/2020
WEBF	WEBFCH07	11/1/2019	11/1/2025
FSTH	FSTH01PH01	11/1/2019	2/1/2020
FSTH	FSTH01PH02	11/1/2019	3/1/2020
FSTH	FSTH01PH03	11/1/2019	4/1/2020
FSTH	FSTH01PH04	1/1/2020	4/1/2020
FSTH	FSTH01PH05	11/1/2019	1/1/2050
FSTH	FSTH01PH06	11/1/2019	3/1/2020
JKMN	JKMN001	10/1/2019	4/1/2021
JKMN	JKMN002	10/1/2019	5/1/2021
JKMN	JKMN003	11/1/2020	7/1/2021

I created measures

Earliest Signed Date =

MIN(CUIDtable[Signed Date])

GLD from Earliest Date =

var _GLDtest =

//get max GLD that is not 1/1/2050

CALCULATE(

    MAX(CUIDtable[GLD]),

    ALLSELECTED(CUIDtable),

    FILTER(CUIDtable, CUIDtable[GLD] < DATE(2050,1,1))

)

var _GLDdate =

// if GLD is later than yesterday (i.e. not yet live) return the earliest GLD

IF(

    _GLDtest >= TODAY(),    

    BLANK(),

    CALCULATE(

        min(CUIDtable[GLD]),

        FILTER(CUIDtable,CUIDtable[Signed Date] = [Earliest Signed Date])

    )

)

Return

_GLDdate

Months Difference =

DATEDIFF([Earliest Signed Date], [GLD from Earliest Date], Month)

Diff Average =

var _table  =

SUMMARIZE(

    CUIDtable,

    CUIDtable[CID],

    "_months", DATEDIFF([Earliest Signed Date], [GLD from Earliest Date], Month)

)

Return

AVERAGEX(

    _table,

    [_months]

)

and ended up with the result

This should get you pointed in the right direction.

[romovaro]

Hi @jgeddes 

Sorry to bother you again with this topic. 

To finalize, I need to create 2 filters

- Region filter to see if number of regions in the scope has an impact on the time?

I have the Column "Region of Implementation Consultant" and I need to create the filter (1 to 6 regions max) to see if more Regions involved = more time.

Value Regions is AMERICA, EMEA, APAC, TBD, GLOBAL and -

Filter should show values from 1 to - 6

- CUID filter : How many CUIDs have every CID (below 5, 5 to 10, 10 to 20, 20 to 30, above 30)?

 I created the Measure but struggling to create the Filter.

CUID Size =

IF('Weekly Slippage'[COUNT CUIDS] <= 5, "1.Up to 5 CUID",

IF('Weekly Slippage'[COUNT CUIDS] <= 10, "2.Between 5 to 10 CUID",

IF('Weekly Slippage'[COUNT CUIDS] <= 20, "3.Between 10 to 20 CUID",

IF('Weekly Slippage'[COUNT CUIDS] <= 30, "4.Between 20 to 30 CUID",

"5.More than 30"

))))

- + - + - + - 

I am trying to filter the totals

Formula used:

 

Diff Average =

var _table =

SUMMARIZE(

'Weekly Slippage',

'Weekly Slippage'[CID],

"_months", DATEDIFF([Earliest Signed Date], [GLD from Earliest Date], Month)

)

Return

AVERAGEX(

_table,

[_months]

)

With "Earliest Signed date" 01/01/2019 but is not possilbe. Any tip?

Thanks

[jgeddes · Accepted solution]

I think the issue is trying to filter with measures, which is not possible.
If you create a calculated column for the CUID size you will then be able to filter by it. 
I added the following column to 'Weekly Slippage'

CUID Size =

var _count =

CALCULATE(

    COUNT('Weekly Slippage'[CUID]),

    ALLEXCEPT('Weekly Slippage','Weekly Slippage'[CID])

)

var _result =

IF(

    _count <=5, "1.Up to 5 CUID",

    IF(

        _count <= 10,

        "2.Between 5 to 10 CUID",

        IF(

            _count <= 20,

            "3.Between 10 to 20 CUID",

            IF(_count <= 30,

            "4.Between 20 to 30 CUID",

            "5.More than 30"

            )

        )

    )

)

Return

_result

Similarly you cannot use the Earliest Signed Date as a filter, but you could put the signed date column into the filters field (either for a specific filter or page or all pages) and then select the signed date you are looking for.

[romovaro]

Thanks Jgeddes!!

Really appreciate your help this week.

Cuid filter worked. THANKS

In my last post i had 2 filter questions in case you can give me one last piece of advice 🙂

I have a column called "Region of Implementation Consultant"

I need to create a Region filter to see if number of regions in the scope has an impact on the time?

Same as the column "#Regions in Scope" but in Filter.

Filter should show 1 (if only onre region), 2 (if 2 regions affected), 3 (if 3 regions), etc..

any help would be appreciated

Thanks

[jgeddes]

How is the # of regions in scope being calculated currently?

[romovaro]

Hi

CUrrently I use the "Region of Implementation Consultant" and I select "distinct Count"

See below some examples:

For CID “ANCL” there are 2 diff regions involved (EMEA & AMERICAS according to the column Region of Impl Consultant). Therefore if i select “2” in the filter, this CID should be included.

For CID “APKS” there are 3 diff regions involved (EMEA, Global and TBD according to the column Region of Impl Consultant). Therefore if i select “3” in the filter, this CID should be included.

For CID “1001041” there are 3 diff regions involved (EMEA, APAC and TBD according to the column Region of Impl Consultant). Therefore if i select “3” in the filter, this CID should be included.

The idea is to create a filter showing 1,2,3,4,5,6 (related to the regions involved in the CID)

Thanks

[jgeddes · Accepted solution]

OK. Similar to the others, if you create a calculated column

# of Regions=

CALCULATE(

    DISTINCTCOUNT('Weekly Slippage'[Region of Implementation Consultant]),

    ALLEXCEPT('Weekly Slippage','Weekly Slippage'[CID])

)

you should then be able to use that column as the filter.

[romovaro]

Happy Friday @cjg 

I have a question regarding this report.

Currently the Months between Signed date and First GLD is showing 7.84.

Diff Average2 =

var _table =

SUMMARIZE(

'Weekly Slippage',

'Weekly Slippage'[CID],

"_months", DATEDIFF([Earliest Signed Date], [GLD from Earliest Date2], Month)

)

Return

AVERAGEX(

_table,

[_months]

)

where:

Earliest Signed Date =

MIN('Weekly Slippage'[Signed])

GLD from Earliest Date2 =

CALCULATE(

CALCULATE(

min('Weekly Slippage'[Ops Forecast & Actuals]),

FILTER('Weekly Slippage','Weekly Slippage'[Signed] = [Earliest Signed Date])

))

At the beg you created the formula below to average ONLY the ones with all CUIDs delivered.

I updated your formula to show ALL CUIDs (delivered or not). 

GLD from Earliest Date =

var _GLDtest =

//get max GLD that is not 1/1/2050

CALCULATE(

MAX('Weekly Slippage'[Ops Forecast & Actuals]),

ALLSELECTED('Weekly Slippage'),

FILTER('Weekly Slippage', 'Weekly Slippage'[Ops Forecast & Actuals] < DATE(2050,1,1))

)

var _GLDdate =

// if GLD is later than yesterday (i.e. not yet live) return the earliest GLD

IF(

_GLDtest >= TODAY(),

BLANK(),

CALCULATE(

min('Weekly Slippage'[Ops Forecast & Actuals]),

FILTER('Weekly Slippage','Weekly Slippage'[Signed] = [Earliest Signed Date])

)

)

Return

_GLDdate

MY issue is that sometimes, filters show higher average in all the options than the standard average:

Example:

I need to show average months for CUID (With Integration and no Integration associated)

I have 2 tables. I created:

Integration CUIDS = SUMMARIZE('Integration CUIDs','Integration CUIDs'[CUID2])

Integration TF = [Integration CUIDS] = 'Weekly Slippage'[CUID]

Integration = IF ('Weekly Slippage'[Integration TF] = True (), "Yes","No")

THen, using this integration FIlter, If yes, average is 8.55 and if No 8.38. WIth nothing selected is 7.84.

Another example I have:

EE Size =

IF('Weekly Slippage'[Contract EE] <= 50, "1.Up to 50 EE",

IF('Weekly Slippage'[Contract EE] <= 200, "2.Between 50-200EE",

IF('Weekly Slippage'[Contract EE] <= 500, "3.Between 200-500EE",

IF('Weekly Slippage'[Contract EE] <= 1000, "4.Between 500-1000EE",

"5.More than 1000 EE"

))))

All my Options have an average bigger than 7.84. (8.25, 9.96, 11.58....) so the average is "weird".

Any idea? thanks and happy Friday

[v-zhangti]

Hi, @romovaro 

Can you provide some sample data for testing with your formula? It is best to include a few special cases you encounter.

Sched-Unsch = Switch( true() ,

[Ops Forecast & Actuals] <= eomonth(Today(),-1), "Done",

eomonth([Ops Forecast & Actuals],0) = eomonth(Today(),0), "Scheduled",

[Ops Forecast & Actuals] > eomonth(Today(),0) && [Ops Forecast & Actuals] <date(2049,12,1) , "Scheduled",

[Ops Forecast & Actuals] = date(3000,12,1) , "Unscheduled",

"Undefined-2050"

)

What is the desired output? It can be displayed in an Excel table.

Best Regards,

Community Support Team _Charlotte

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

[romovaro]

Hi v-zhangti,

Thanks for your email. The table has a column called CID (customer name) and a CUID (different entities from the customer)

The idea of this report is to show the diff in Months between the earliest Contract Signed date and the earliest Implementation Date (Earliest Go Live Date). 

For the example below, Customer with CID (MSFT) , the time from the earliest Signed date to first Implementation was 6 months.

I need to show the difference between the earliest Signed Date and the Earliest GLD from every CID using all their CIUDs.

The second part of this report is to create a filter to show ONLY the customers with ALL CUIDs Delivered (showing GLD before current month or 01/01/2050).

In the examples below, Example 1 should be included in the filter because ALL CUIDS were delivered. For the example 2, there is still one CUID pending, therefore not included in the filter.

Hope this helps

thanks