cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
romovaro
Post Patron
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)

 

romovaro_1-1663146295871.png

 

 

 

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

Based on your example I created the dataset
CUIDtable

CIDCUIDSigned DateGLD

WEBFWEBFCH0111/1/20192/1/2020
WEBFWEBFCH0211/1/20193/1/2020
WEBFWEBFCH0311/1/20194/1/2020
WEBFWEBFCH041/1/20204/1/2020
WEBFWEBFCH0511/1/20191/1/2050
WEBFWEBFCH0611/1/20193/1/2020
WEBFWEBFCH0711/1/201911/1/2025
FSTHFSTH01PH0111/1/20192/1/2020
FSTHFSTH01PH0211/1/20193/1/2020
FSTHFSTH01PH0311/1/20194/1/2020
FSTHFSTH01PH041/1/20204/1/2020
FSTHFSTH01PH0511/1/20191/1/2050
FSTHFSTH01PH0611/1/20193/1/2020
JKMNJKMN00110/1/20194/1/2021
JKMNJKMN00210/1/20195/1/2021
JKMNJKMN00311/1/20207/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
 
jgeddes_1-1663351313924.png

This should get you pointed in the right direction.

 

 

View solution in original post

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.
 
 

View solution in original post

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.

View solution in original post

10 REPLIES 10
jgeddes
Solution Sage
Solution Sage

Based on your example I created the dataset
CUIDtable

CIDCUIDSigned DateGLD

WEBFWEBFCH0111/1/20192/1/2020
WEBFWEBFCH0211/1/20193/1/2020
WEBFWEBFCH0311/1/20194/1/2020
WEBFWEBFCH041/1/20204/1/2020
WEBFWEBFCH0511/1/20191/1/2050
WEBFWEBFCH0611/1/20193/1/2020
WEBFWEBFCH0711/1/201911/1/2025
FSTHFSTH01PH0111/1/20192/1/2020
FSTHFSTH01PH0211/1/20193/1/2020
FSTHFSTH01PH0311/1/20194/1/2020
FSTHFSTH01PH041/1/20204/1/2020
FSTHFSTH01PH0511/1/20191/1/2050
FSTHFSTH01PH0611/1/20193/1/2020
JKMNJKMN00110/1/20194/1/2021
JKMNJKMN00210/1/20195/1/2021
JKMNJKMN00311/1/20207/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
 
jgeddes_1-1663351313924.png

This should get you pointed in the right direction.

 

 

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

romovaro_3-1663924851112.png

 

 

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

))))

 

 

 

romovaro_2-1663924806175.png

 

 

- + - + - + - 

 

I am trying to filter the totals

 

romovaro_0-1663936157517.png

 

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?
 
romovaro_1-1663936233701.png

 

 
 
Thanks

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.
 
 

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.

 

romovaro_1-1663941987264.png

 

romovaro_2-1663942158106.png

 

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

 

any help would be appreciated

 

Thanks

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

Hi

 

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

 

romovaro_1-1664104300262.png

 

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.

 

romovaro_0-1664199055465.png

 

 

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.

romovaro_1-1664199055460.png

 

 

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.

romovaro_2-1664199055484.png

 

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

 

Thanks

 

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.

Happy Friday @cjg 

 

I have a question regarding this report.

romovaro_1-1666338563483.png

 

 

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:

romovaro_2-1666339466496.png

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

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.

romovaro_0-1663318041620.png

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.

 

romovaro_1-1663318079944.png

 

Hope this helps

 

thanks

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Dashboard in a day with date

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!

Power BI Fabric Summit Carousel

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