Project Cancelled date Measure

[chitti5]

Hello All,

I reached out to this forum a couple of times. but none could help me with correct solution. Please help me as this is kinda urgent!!!

I need 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. One helper helped me with a measure but it didn't work. Pasting screenshots with explanation below and also a sample table.

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

Please helpppp

[TomMartens · Accepted solution]

Hey @chitti5 ,

because you do not use a star schema with fact and dimension tables; instead, you use a single-table solution (what I personally call the dreaded single-table solution), a measure becomes more convoluted because you can not navigate easily from the selected cancelled date into the past.

This measure, does not consider leapyears and the difference between 30 and 31 days months (all this is not necessary if there is a dedicated calendar dimension table):

Measure = 
var noOfPreviousMonth = 3

return
SUMX(
    SUMMARIZE(
        'Table'
        , 'Table'[Client Country]
        , 'Table'[Client State]
        , 'Table'[Project Cancelled date]
    )
    , var currentCountry = 'Table'[Client Country]
    var currentState = 'Table'[Client State]
    var currentDate = CALCULATE( MINX( VALUES( 'Table'[Project Cancelled date] ) , 'Table'[Project Cancelled date] ) )
    var currentYear = YEAR( currentDate )
    var currentMonth = MONTH( currentDate )
    var currentDay = DAY( currentDate )
    var previousDate = 
        IF( currentMonth <= 3
            , DATE( currentyear - 1, 12 - (currentMonth - noOfPreviousMonth ) , currentDay )
            , DATE( currentYear, currentMonth - noOfPreviousMonth, currentDay )
        )
    var filterDates = 
        DATESBETWEEN( 'Table'[Project Cancelled date] , previousDate , currentDate )
    return
    
    CALCULATE(
        DISTINCTCOUNT('Table'[Project ID] )
        , 'Table'[Status] = "Cancelled"
        , ALLEXCEPT( 'Table' , 'Table'[Client Country] , 'Table'[Client State] )
        , filterDates
    )
)

But it returns the expected result:

Hopefully, this provides what you are looking for.

I strongly recommend learning to use a star schema that at least uses dimension tables for "objects" that are used in slicers, here "region" and "date."

Regards,

Tom

[chitti5]

Hi, @DataNinja777 - Please find the sample data with correct date format. Please suggest a measure to find out the correct solution.

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	4/14/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

Thank you.

[DataNinja777]

Hi @chitti5 ,

Sorry, this is not a solution, and just FYI, but your sample data 4th line shows as follows which doesn't seem to be consistent with the m/d/yyyy format in the rest of the sample table date format.  

14/4/2024

Best regards,