Number of Projects got cancelled

[h11]

Hi, I have 18 projects which got cancelled from 2022 to till date. I'm creating a report in Power BI.

I want to know DAX formulas to find out:
1. No of projects that got cancelled in last 3 months from the date it started.
2. No of projects that got cancelled in last 12 months from the date it started.

Here is a sample data for your reference.

Project ID	Start Date	Status
1	Friday, February 23, 2024	Cancelled
2	Friday, February 23, 2024	Cancelled
3	Friday, April 5, 2024	Cancelled
4	Friday, April 14, 2023	Cancelled
5	Friday, March 22, 2024	Cancelled
6	Friday, April 1, 2022	Cancelled
7	Friday, August 25, 2023	Cancelled
8	Friday, August 25, 2023	Cancelled
9	Friday, December 15, 2023	Cancelled
10	Monday, April 8, 2024	Cancelled
11	Friday, April 1, 2022	Cancelled
12	Friday, December 15, 2023	Cancelled
13	Friday, April 14, 2023	Cancelled
14	Friday, August 1, 2022	Cancelled
15	Friday, December 1, 2022	Cancelled
16	Friday, April 1, 2022	Cancelled
17	Friday, April 14, 2023	Cancelled
18	Friday, December 22, 2023	Cancelled

Thank you.

[v-yangliu-msft · Accepted solution]

Thanks for your replies, these have been a great help to me, please allow me to offer another insight:

Hi  @h11 ,

Are you referring to displaying the count of the first 3 months or the first 12 months of each date line grouped by [Client Country] and [Client Country]?

You can use the following measure：

last_3 =
COUNTX(
    FILTER(ALL('Table'),
    'Table'[Client Country]=MAX('Table'[Client Country])&&
    'Table'[Client State]=MAX('Table'[Client State])&&
    'Table'[Project Cancelled date]>=DATE(YEAR(MAX('Table'[Project Cancelled date])),MONTH(MAX('Table'[Project Cancelled date]))-3,DAY(MAX('Table'[Project Cancelled date])))&&
    'Table'[Project Cancelled date]<=MAX('Table'[Project Cancelled date])&&
    'Table'[Status]="Cancelled"
    ),[Project ID])

last_12 =
COUNTX(
    FILTER(ALLSELECTED('Table'),
    'Table'[Client Country]=MAX('Table'[Client Country])&&
    'Table'[Client State]=MAX('Table'[Client State])&&
    'Table'[Project Cancelled date]>=DATE(YEAR(MAX('Table'[Project Cancelled date]))-1,MONTH(MAX('Table'[Project Cancelled date])),DAY(MAX('Table'[Project Cancelled date])))&&
    'Table'[Project Cancelled date]<=MAX('Table'[Project Cancelled date])&&
    'Table'[Status]="Cancelled"
    ),[Project ID])

Result:

Best Regards,

Liu Yang

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

[h11]

Hi @miTutorials @IoannisPhilip @HotChilli @hackcrr - I'm sorry for misleading with my question. Here is my clear ask. Sincere apologies for wasting your time.

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. 

 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

[miTutorials]

Try the below measure - You can combine with field parameter to make the Month selection dynamically from a slicer. If you dont know how to do that, watch the below tutorial from 8:40 onwards.

Dynamic Segmentation: Identifying Top & Bottom N% Customers by Sales in Power BI | MiTutorials (yout...

CancelledOrders = VAR TodayDate = TODAY()
Var StartDate  = EDATE(TodayDate,-12)
RETURN
CALCULATE(
    COUNTROWS('Orders'),
    'Orders'[Status] = "Cancelled",
    'Orders'[Date] >= StartDate,
    'Orders'[Date] <= TodayDate)

[IoannisPhilip]

Hi @h11 ,
You can create two calculated columns using DAX formulas that will help you determine the number of projects that got cancelled within the specified time frames from their start dates. Here’s how you can do it:
Step 1: You need an "End Date / Cancellation Date" Column
Step 2: Create a calculated column 

CancelledIn3Months = 
VAR CancelledDate = [End Date]  // assuming this is the cancellation date
VAR StartDate = [Start Date]
VAR IsCancelledIn3Months = 
    IF(
        [Status] = "Cancelled" &&
        DATEDIFF(StartDate, CancelledDate, MONTH) <= 3,
        1,
        0
    )
RETURN
    IsCancelledIn3Months

 Do the same for the 12 months
 Step 3: Create equivalent measures 

CancelledProjectsIn3Months = 
CALCULATE(
    COUNTROWS(TableName),
    TableName[CancelledIn3Months] = 1
)

If you want a dynamic measure consider using the today() function instead.

Best wishes,
Ioannis

[HotChilli]

Can you provide more info please?

The date column is the cancellation date or the date the project started?

You say "from the date it started", do we need another date here for each row or is there an assumption of 1/1/2022?

Please show the desired output from this data.

[hackcrr]

Hi, @h11 

To calculate the number of projects canceled in the last 3 months and the last 12 months from the start date, you can create a DAX metric in Power BI. First, you need a column to indicate when a project was canceled. Since all of the items in the example data are marked as canceled, you can create a column that contains the date of the cancellation. For simplicity, assume that the cancel date is the same as the start date.

Cancellation Date = Projects[Start Date]

Number of projects canceled within 3 months DAX Metric Expression:

Cancelled within 3 Months = 
CALCULATE(
    COUNT(Projects[Project ID]),
    FILTER(
        Projects,
        Projects[Status] = "Cancelled" &&
        DATEDIFF(Projects[Start Date], Projects[Cancellation Date], MONTH) <= 3
    )
)

Number of projects canceled within 12 months:

Cancelled within 12 Months = 
CALCULATE(
    COUNT(Projects[Project ID]),
    FILTER(
        Projects,
        Projects[Status] = "Cancelled" &&
        DATEDIFF(Projects[Start Date], Projects[Cancellation Date], MONTH) <= 12
    )
)

Add card visual: Drag the Cancelled within 3 Months metric onto a card to show the number of items canceled within the last 3 months. Add another card visual: Drag the Cancelled within 12 Months metric to another card to show the number of items canceled within the last 12 months.

Best Regards,

hackcrr

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