Help with complex DAX measure

[idrabble]

Hi everyone,

after spending a few hours going round in circles on this one, I thought it was time to ask the experts

My company measures performance of new customers in terms of a conquest.

A conquest is defined as a cutomer who has spent more than $500 with us this fiscal year (July 1 to Jun 30) but spent $0 with us last fiscal year.

I would like to demonstrate this as simply as using a Card.

How would I move forward on this>

Thanks, Ian

[GilbertQ]

Hi @idrabble

Just so that I understand the definition of a conquest is the following:

Customer must have spent >= $500 in the current Fiscal Year, but spend $0 in the previous Fiscal Year?

If that is correct, I would create on measure which would calculate their spend in the Current Fiscal Year. In order to this I would have a Date table which contained the dates for my Fiscal Year. I would then create a measure which calculated their Sales for the Current Fiscal Year.

CFY Spend = TOTALYTD(sum('Table'[Sales Amount]),'Date'[Calendar Date],ALL('Date'),"06/30")

For the above you would replace the SUM() with your spend amounts. 

Then I have called my Date Table 'Date', and my column which I create the relationship between my 'Date' table and 'Table' on the [Calendar Date]

Then the last piece is when your financial year ends. As with my example here in Australia it ends on 30 June.

Next in order to create the previous years spend I would use the following measure.

PFY Spend = CALCULATE([CFY Spend],SAMEPERIODLASTYEAR('Date'[Calendar Date]))

You will see that I have now used my Previous measure "CFY Spend" in this calculation, and this is because I have already defined the Total Year to date, so using this measure in combination with the SAMEPERIODLASTYEAR means that it will go back 1 Year and get the values for the Previous year.
Once again above is the Table Name, the spend amounts.

And then my Date table

Now that I have got the Current Year spend [CFY Spend] and the Previous Years spend [PFY Spend], I can then do a final calculation dependant on what you want the outcome to be?

It could just be the differennce, or you could do a count based on the difference, or you could use the SWITCH command depnding on how you want to define it.

Roughly this measure below, but I have written it off the top of my head so it might be a little off.

Customer Conquest = COUNT(IF([CFY Spend] >= 500 && [PFY Spend] = 0,1,0)

[Sean]

I'm assuming you have a 'Calendar Table'

If you don't you can create one by clicking New Table and typing - Calendar Table = CALENDARAUTO ( )

Then create these 3 Measures

Measure 1

FYTD Measure = TOTALYTD ( SUM ( 'Table'[Sales] ), 'Calendar Table'[Date], "06/30" )

Measure 2

PFYTD MEASURE = CALCULATE ( [FYTD Measure], DATEADD ( 'Calendar Table'[Date], - 1, YEAR ) )

Measure 3

Conquest Customers =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER (
        ALLSELECTED ( 'Table'[Customer] ),
        [FYTD Measure] >= 500
            && [PFYTD MEASURE] = 0
    )
)

This final Measure should give you the number of customers who had 0 last FY and have over 500 this FY.

Hope this helps!

[idrabble]

I am still unable to get this to work.

I have tried everything suggested so far with no success, so I thought I would go back to basics

By using the following I can get a Matrix to function almost correctly:

2016 <1 = if(Measures_Table[Revenue (-1Yr)]<1,1,0)     -- where [Revenue (-1Yr)] = CALCULATE(sum('Sales Data Last 5 years'[Revenue AUD]),FILTER('Calendar','Calendar'[FiscalYear]=[Current Fiscal Yr -1]))

2017 >499 = if([Revenue This Yr]>499,1,0)    -- where [Revenue This Yr] = CALCULATE(sum('Sales Data Last 5 years'[Revenue AUD]),FILTER('Calendar','Calendar'[FiscalYear]=[Current Fiscal Yr]))

Then  Conquest Customer = if(and(Measures_Table[2016 <1]=1,Measures_Table[2017 >499]=1),1,0) 

If I then drop this into a Matrix of all Customers I can identify which are conquests as they are defined correctly as 1's

However, even though these values are formatted as whole numbers, they do tally correctly. I have seven 1's but get a total at the bottom of the Matrix of 0

Now obviously when I use the measure in a Card, I still get 0

Any further help would be appreciated

Ian

[GilbertQ · Accepted solution]

Hi @idrabble

From my understanding that would be due to the fact that when the measure is being calculated in the Matrix it is doing it in the row context of the matrix.

So when the total is calculated there is no row context, so the measure is then defaulting to the zero condition of the measure.

If you want this to be calculated correctly, my best advice is you will need to do the following example from the Power Pivot Pro, which explains in detail how to get this working as expected.

Subtotals and Grand Totals That Add Up “Correctly”

[idrabble]

Many thanks @GilbertQ

Using the principles of that solution worked.

Can't thank you enough for your help

Ian

[GilbertQ]

Glad it helped you out, and happy to assist.

[idrabble]

HI @GilbertQ

I will have a look at that solution

Many thanks for your help again

Ian

[idrabble]

Many thanks @GilbertQ

I do have the first 2 expressions:

YTD Total Revenue = TOTALYTD([Total Revenue],'Calendar'[CalendarDate],All('Calendar'),"30/06")

YTD last Year Total Revenue = CALCULATE([YTD Total Revenue],SAMEPERIODLASTYEAR('Calendar'[CalendarDate]))

However, your expression for count of conquests gives me the following error:

The COUNT function only accepts a column reference as an argument

Thanks, Ian

[GilbertQ]

Hi @idrabble

Possibly you might even be able to simply use the SUM DAX Function, because looking at it again, if it is TRUE or valid it gives it a value of 1, and not not 0.

So the SUM should then work correctly?