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

Forum Discussion

romovaro

Responsive Resident

4 years ago

Solved

Average in Dates with delivered projects

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

Need Help

jgeddes

4 years ago

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
4 years ago
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
4 years ago
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.

10 Replies

jgeddes

Super User

4 years ago

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
Responsive Resident
4 years ago
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
  Super User
  4 years ago
  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.

v-zhangti
Community Support
4 years ago
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
  Responsive Resident
  4 years ago
  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

Forum Discussion

Average in Dates with delivered projects

10 Replies

Related content

Average and Moving Average

Weekly Average

Missing Project Name

Accumulated average

learn fabric with hands on project Project