cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Post Patron

## 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

3 ACCEPTED SOLUTIONS
Solution Sage

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.

Solution Sage

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.

Solution Sage

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 10
Solution Sage

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.

Post Patron

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
Solution Sage

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.

Post Patron

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

Solution Sage

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

Post Patron

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

Solution Sage

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.
Post Patron

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
Community Support

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.

Post Patron

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors