topic Re: Count projects where CY20 total spend is zero in DAX Commands and Tips

Count projects where CY20 total spend is zero

Anonymous — Thu, 29 Oct 2020 05:35:38 GMT

Hi,

I am trying to write a DAX measure to count the no. of projects where the NET total spend this year is ZERO.

It mostly works but I have projects where they spend say $100 in February 2020, then -$100 in Nov 2020 - leading to a net total spend of ZERO - however, my DAX counts this project as a project that has non-zero spend.

In the table below - the no. of projects with zero net spend in 2020 should be two projects i.e. AAA and CCC

But my measure counts one project i.e. CCC

Here are my two measures - the second measure allows me to calculate 2020 spend.

Project Count = calculate(

DISTINCTCOUNT('[Project ID])

,FILTER('Table', [CY20 Spend]=0)

)

CY20 Spend=

CALCULATE(

SUM([Value]),

'Table'[Date].[Year] = 2020

)

Project ID	Date	Value ($)
AAA	1/1/2020	-100
AAA	1/3/2020	50
AAA	1/4/2020	50
BBB	1/1/2020	50
CCC	1/1/2020	0
CCC	1/2/2020	0

Re: Count projects where CY20 total spend is zero

wdx223_Daniel — Thu, 29 Oct 2020 06:01:20 GMT

Anonymous

Project Count = calculate(

DISTINCTCOUNT('[Project ID])

,FILTER(ALL('Table'[Project ID]), [CY20 Spend]=0)

)

Re: Count projects where CY20 total spend is zero

amitchandak — Thu, 29 Oct 2020 06:09:20 GMT

Anonymous , Try a measure like

countx(filter(summarize('Table', 'Table'[Project ID], "_1",[CY20 Spend]+0),[_1]=0),[Project ID])

I assumed your measure CY20 Spend working correctly

or try measure like with date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.