Forum Discussion
Create a Meassure using another meassure as parameter
hi i have the following table
| Id | Month | NewPurchases |
| 1 | 2014-06 | 0 |
| 2 | 2014-07 | 0 |
| 3 | 2014-08 | 13452 |
| 11 | 2015-04 | 0 |
| 12 | 2015-05 | 0 |
| 13 | 2015-06 | 16142.4 |
| 23 | 2016-04 | 18683.32586 |
| 24 | 2016-05 | 0 |
| 25 | 2016-06 | 16142.4 |
what i need is to have 2 meassures:
The first one will show the last month purchase and the second one will show the Ammount of that purchase
to calculate the last purchase month i created this meassure
Last Purchase Month = CALCULATE(MAXX('Summaries','Summaries'[Month]),Summaries[NewPurchases]>0)
this works fine(if you have an idea to make this better will be apreciated)
but when i try to create the other meassure that returns the ammount on the new purchase i always get the sum of all the elements even when i am using a filter,
Last Purchase Ammount = CALCULATE(sum(Summaries[NewPurchases]),filter(Summaries,Summaries[Month]=(Summaries[Last Purchase Month])))
again i am pretty sure that theres a lot of room to make this in a better way.
Thanks a lot
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])))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 ) )
3 Replies
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])))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.
