Forum Discussion
Help with complex DAX measure
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.
Super UserHi 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)
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