Project Cancelled date Measure

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