Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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
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.
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,