Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Count projects where CY20 total spend is zero

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 IDDateValue ($)
AAA1/1/2020-100
AAA1/3/202050
AAA1/4/202050
BBB1/1/202050
CCC1/1/20200

CCC

1/2/20200

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    Project Count = calculate(
    DISTINCTCOUNT('[Project ID])
    ,FILTER(ALL('Table'[Project ID]), [CY20 Spend]=0)
    )
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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.