Forum Discussion
Helper IIICount of Project getting cancelled
Hi h11 ,
I used both methods and you can check the results below:
Measure = VAR _MINDATE = MINX(FILTER(ALL('Table'),'Table'[Client State] = MAX('Table'[Client State]) && 'Table'[Client Country] = MAX('Table'[Client Country]) && 'Table'[Status] = "Cancelled"),'Table'[Project Cancelled date]) VAR _COUNT = CALCULATE(COUNT('Table'[Project ID]),FILTER(ALLEXCEPT('Table','Table'[Client State],'Table'[Client Country]),'Table'[Status] = "Cancelled" && 'Table'[Project Cancelled date] <= EDATE(_MINDATE,6))) RETURN _COUNT Measure 2 = var _t = ADDCOLUMNS(ALLSELECTED('Table'),"Min Date",MINX(FILTER(ALLSELECTED('Table'),[Client State]=EARLIER([Client State])&&[Client Country]=EARLIER([Client Country])&&[Status]="Cancelled"),[Project Cancelled date])) var _t2 = ADDCOLUMNS(_t,"Count",COUNTX(FILTER(_t,[Client State]=EARLIER([Client State])&&[Client Country]=EARLIER([Client Country])&&[Status]="Cancelled"&&[Project Cancelled date]<EDATE([Min Date],6)),[Project ID])) RETURN SUMX(FILTER(_t2,[Project ID] = MAX('Table'[Project ID])),[Count])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi h11 ,
I made simple samples, but not the final version because the data you provided cannot be converted to date format:
Measure = var _t = ADDCOLUMNS('Table',"a",COUNTAX(FILTER(ALL('Table'),[Client State]=EARLIER([Client State])&&[Client Country]=EARLIER([Client Country])&&[Status]="Cancelled"),[Project ID]))
RETURN MAXX(_t,[a])If you need it by date, you can try:
Measure = var _t = ADDCOLUMNS('Table',"a",COUNTAX(FILTER(ALL('Table'),[Project Cancelled date]>EOMONTH(EARLIER([Project Cancelled date]),-3)&&[Client State]=EARLIER([Client State])&&[Client Country]=EARLIER([Client Country])&&[Status]="Cancelled"),[Project ID]))
RETURN MAXX(_t,[a])
An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
h11Hi Anonymous : Thank you for the measure and sharing a sample file. I hope I'm almost getting there. But I'm not able to understand if this measure is for 3 months or 12 months? I need 2 measures to show cancelled projects of a client for the last 3 months count and 12 months count from the cancelled date.
Hi h11 ,
The second measure is within 3 months, if you need it within 12 months, change -3 to -12.
Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- h11
Hi Anonymous I tried the measure you suggested for 3 months only. However, I didn't get the correct results. I'm sharing a screenshot along with the table which has date format. Please look at the screenshot and suggest the correct formula. Thank you.
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