cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Responsive Resident

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

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.

Proud to be a Super User!

Super User

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.

Proud to be a Super User!

Super User

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.

Proud to be a Super User!

10 REPLIES 10
Super User

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.

Proud to be a Super User!

Responsive Resident

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
Super User

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.

Proud to be a Super User!

Responsive Resident

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

Super User

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

Proud to be a Super User!

Responsive Resident

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

Super User

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.

Proud to be a Super User!

Responsive Resident

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.

Responsive Resident

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors