Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chitti5
Helper I
Helper I

Project Cancelled date Measure

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.

 

1.png2.png

Project IDProject Cancelled dateClient CountryClient StateStatus
12/23/2024IndiaHyderabadCancelled
22/23/2024IndiaBengaluruCancelled
34/5/2023IndiaHyderabadCancelled
414/4/2024IndiaHyderabadCancelled
53/22/2024IndiaHyderabadCancelled
64/1/2022IndiaBengaluruCancelled
78/25/2022IndiaBengaluruCancelled
88/25/2023IndiaHyderabadCancelled
912/15/2023USAAtlantaCancelled
104/8/2024USAVirginiaActive
114/1/2024USAVirginiaCancelled
1212/15/2023USAAtlantaCancelled
134/15/2024IndiaHyderabadCancelled
148/1/2022IndiaHyderabadCancelled
1512/1/2022IndiaHyderabadCancelled
164/1/2022IndiaBengaluruCancelled
174/14/2023IndiaBengaluruCancelled
1812/25/2023IndiaHyderabadCancelled

 

Please helpppp

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
chitti5
Helper I
Helper I

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

 

Project IDProject Cancelled dateClient CountryClient StateStatus
12/23/2024IndiaHyderabadCancelled
22/23/2024IndiaBengaluruCancelled
34/5/2023IndiaHyderabadCancelled
44/14/2024IndiaHyderabadCancelled
53/22/2024IndiaHyderabadCancelled
64/1/2022IndiaBengaluruCancelled
78/25/2022IndiaBengaluruCancelled
88/25/2023IndiaHyderabadCancelled
912/15/2023USAAtlantaCancelled
104/8/2024USAVirginiaActive
114/1/2024USAVirginiaCancelled
1212/15/2023USAAtlantaCancelled
134/15/2024IndiaHyderabadCancelled
148/1/2022IndiaHyderabadCancelled
1512/1/2022IndiaHyderabadCancelled
164/1/2022IndiaBengaluruCancelled
174/14/2023IndiaBengaluruCancelled
1812/25/2023IndiaHyderabadCancelled

 

Thank you.

DataNinja777
Super User
Super User

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,

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.