Forum Discussion
Create a Meassure using another meassure as parameter
- 9 years ago
Hi, this is something that I recently learned in the Definitive Guide to DAX (which I cannot recommend enough): measures always perform a context transition. What you need to do is use the same MAX logic in the 2nd measure. Notice I also changed the MAXX to MAX because there is nothing to iterate over. I think the two are equivalent in this scenario but this is more readable.
Last Purchase Month = CALCULATE(MAX('Summaries'[Month]), all(Summaries),Summaries[NewPurchases]>0) Last Purchase Amount = CALCULATE(sum(Summaries[NewPurchases]),all(Summaries), filter(Summaries,Summaries[Month]=MAX('Summaries'[Month]))
This is something about DAX that would drive me crazy because I never understood why replacing a reference to a measure with its identical logic would make things suddenly work.
Oh and I changed the filter condition on the Last Purchase Month so that it woudl return something even if the table was filtered to a month that had no sales. You might want to update the logic to include the last purchase date up to the date that is currently selected by using logic similar to the YTD pattern:
Last Purchase Amount = CALCULATE(sum(Summaries[NewPurchases]),all(Summaries), FILTER(Summaries,Summaries[Month]=MAX('Summaries'[Month]) && Summaries[NewPurchases]>0 && 'Summaries'[Month] <= MAX ( 'Summaries'[Month] ))) Last Purchase Month = CALCULATE(MAX('Summaries'[Month]), all(Summaries),Summaries[NewPurchases]>0, FILTER(Summaries,Summaries[Month]<=MAX(Summaries[Month]))) - 9 years ago
I think the accepted solution may not work if the last month is zero. My appraoch is to first create a base measure to sum the purchases and reference it in the Last Purchase Amount measure which relies on the LASTNONBLANK function. Hopefully they work for you.
Amount Spent=SUM ( Summaries[NewPurchases] )
Last Purchase Month=CALCULATE ( MAX ( Summaries[Month] ), Summaries[NewPurchases] > 0 )
Last Purchase Amount=[Amount Spent] (LASTNONBLANK(Summaries[Month] , [Amount Spent] (Summaries[NewPurchases] > 0 ) / 1 ) )
I think the accepted solution may not work if the last month is zero. My appraoch is to first create a base measure to sum the purchases and reference it in the Last Purchase Amount measure which relies on the LASTNONBLANK function. Hopefully they work for you.
Amount Spent=SUM ( Summaries[NewPurchases] )
Last Purchase Month=CALCULATE ( MAX ( Summaries[Month] ), Summaries[NewPurchases] > 0 )
Last Purchase Amount=[Amount Spent] (LASTNONBLANK(Summaries[Month] , [Amount Spent] (Summaries[NewPurchases] > 0 ) / 1 ) )
you were right, thanks a lot! regards.