Count of Project getting cancelled

[h11]

Hello All, I want to know the number of projects that got cancelled with the Same Client country and client state for the last 3 months and 12 months from the date it got cancelled and when I drill through it should be able to show the complete details of each project that showed the count.

 FYI - I have 120+ projects that got cancelled from 2022. My table also includes Active projects. This is just a sample data with just 17 cancelled projects and 1 active project for your reference.

Project ID	Project Cancelled date	Client Country	Client State	Status
1	Friday, February 23, 2024	India	Hyderabad	Cancelled
2	Friday, February 23, 2024	India	Bengaluru	Cancelled
3	Friday, April 5, 2024	India	Hyderabad	Cancelled
4	Friday, April 14, 2023	India	Hyderabad	Cancelled
5	Friday, March 22, 2024	India	Hyderabad	Cancelled
6	Friday, April 1, 2022	India	Bengaluru	Cancelled
7	Friday, August 25, 2023	India	Bengaluru	Cancelled
8	Friday, August 25, 2023	India	Hyderabad	Cancelled
9	Friday, December 15, 2023	USA	Atlanta	Cancelled
10	Monday, April 8, 2024	USA	Virgina	Active
11	Friday, April 1, 2022	USA	Virginia	Cancelled
12	Friday, December 15, 2023	USA	Atlanta	Cancelled
13	Friday, April 14, 2023	India	Hyderabad	Cancelled
14	Friday, August 1, 2022	India	Hyderabad	Cancelled
15	Friday, December 1, 2022	India	Hyderabad	Cancelled
16	Friday, April 1, 2022	India	Bengaluru	Cancelled
17	Friday, April 14, 2023	India	Bengaluru	Cancelled
18	Friday, December 22, 2023	India	Hyderabad	Cancelled

[v-tianyich-msft · Accepted solution]

Hi @h11 ,

I used both methods and you can check the results below:

Measure = 
VAR _MINDATE = MINX(FILTER(ALL('Table'),'Table'[Client State] = MAX('Table'[Client State]) && 'Table'[Client Country] = MAX('Table'[Client Country]) && 'Table'[Status] = "Cancelled"),'Table'[Project Cancelled date])
VAR _COUNT = CALCULATE(COUNT('Table'[Project ID]),FILTER(ALLEXCEPT('Table','Table'[Client State],'Table'[Client Country]),'Table'[Status] = "Cancelled" && 'Table'[Project Cancelled date] <= EDATE(_MINDATE,6)))
RETURN
_COUNT

Measure 2 = 
var _t = ADDCOLUMNS(ALLSELECTED('Table'),"Min Date",MINX(FILTER(ALLSELECTED('Table'),[Client State]=EARLIER([Client State])&&[Client Country]=EARLIER([Client Country])&&[Status]="Cancelled"),[Project Cancelled date]))
var _t2 = ADDCOLUMNS(_t,"Count",COUNTX(FILTER(_t,[Client State]=EARLIER([Client State])&&[Client Country]=EARLIER([Client Country])&&[Status]="Cancelled"&&[Project Cancelled date]<EDATE([Min Date],6)),[Project ID]))
RETURN SUMX(FILTER(_t2,[Project ID] = MAX('Table'[Project ID])),[Count])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[v-tianyich-msft]

Hi @h11 ,

I made simple samples, but not the final version because the data you provided cannot be converted to date format:

Measure = var _t = ADDCOLUMNS('Table',"a",COUNTAX(FILTER(ALL('Table'),[Client State]=EARLIER([Client State])&&[Client Country]=EARLIER([Client Country])&&[Status]="Cancelled"),[Project ID]))
RETURN MAXX(_t,[a])

If you need it by date, you can try:

Measure = var _t = ADDCOLUMNS('Table',"a",COUNTAX(FILTER(ALL('Table'),[Project Cancelled date]>EOMONTH(EARLIER([Project Cancelled date]),-3)&&[Client State]=EARLIER([Client State])&&[Client Country]=EARLIER([Client Country])&&[Status]="Cancelled"),[Project ID]))
RETURN MAXX(_t,[a])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[h11]

Hi @v-tianyich-msft : Thank you for the measure and sharing a sample file. I hope I'm almost getting there. But I'm not able to understand if this measure is for 3 months or 12 months? I need 2 measures to show cancelled projects of a client for the last 3 months count and 12 months count from the cancelled date. 

[v-tianyich-msft]

Hi @h11 ,

The second measure is within 3 months, if you need it within 12 months, change -3 to -12.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[h11]

Hi @v-tianyich-msft I tried the measure you suggested for 3 months only. However, I didn't get the correct results. I'm sharing a screenshot along with the table which has date format. Please look at the screenshot and suggest the correct formula. Thank you.

Project ID	Project Cancelled date	Client Country	Client State	Status
1	2/23/2024	India	Hyderabad	Cancelled
2	2/23/2024	India	Bengaluru	Cancelled
3	4/5/2023	India	Hyderabad	Cancelled
4	14/4/2024	India	Hyderabad	Cancelled
5	3/22/2024	India	Hyderabad	Cancelled
6	4/1/2022	India	Bengaluru	Cancelled
7	8/25/2022	India	Bengaluru	Cancelled
8	8/25/2023	India	Hyderabad	Cancelled
9	12/15/2023	USA	Atlanta	Cancelled
10	4/8/2024	USA	Virginia	Active
11	4/1/2024	USA	Virginia	Cancelled
12	12/15/2023	USA	Atlanta	Cancelled
13	4/15/2024	India	Hyderabad	Cancelled
14	8/1/2022	India	Hyderabad	Cancelled
15	12/1/2022	India	Hyderabad	Cancelled
16	4/1/2022	India	Bengaluru	Cancelled
17	4/14/2023	India	Bengaluru	Cancelled
18	12/25/2023	India	Hyderabad	Cancelled

[h11]

@amitchandak Thanks for your suggestion. I didn't the required output. Please see below screenshot.

I applied the measure for 3 months you suggested. I wanted to see the count of cancelled projects of clients with same name and city for last 3 months from the cancelled date. but I got all quantity as 1 each. Ideally I should get the results for feb 13th as 3 since there are 3 projets with same client name and client city for last 3 months. Same goes to 12 months of data. Thank you!

[amitchandak]

@h11 , Based on what I got, try measure like

Cancelled Projects Last 3 Months =
CALCULATE(
COUNTROWS('Table'),
'Table'[Status] = "Cancelled",
'Table'[Project Cancelled date] > eomonth(today(), -3)
)

Cancelled Projects Last 12 Months =
CALCULATE(
COUNTROWS('Table'),
'Table'[Status] = "Cancelled",
'Table'[Project Cancelled date] > eomonth(today(), -12)
)