I am new to this DAX language and I am sure the solution isa very simple one, but I would appreciate some help.<o></o>
<o> </o>
I have created two measures. <o></o>
MAX MONTH=MONTH(MAX('MONTHLYSALES 002'[Date]))
2016 ADJ NET SALES-YTD= CALCULATE(SUM([ADJUSTED NET SALES]), 'MONTHLYSALES002'[CAL_YEAR]=2016,'MONTHLYSALES 002'[PERIOD]<=11)<o></o>
Which gives me the desired results in a Pivot table<o></o>
2016 ADJ NET SALES-TOTAL | 2016 ADJ NET SALES-YTD | 2017 ADJ NET SALES- YTD | YTD % CHNG | MAX MONTH | |
FLEA & TICK | $34,273,033 | $32,747,351 | $34,925,113 | 6.7 % | 11 |
HEALTH AND BEAUTY CARE | $7,262,615 | $6,407,594 | $8,012,576 | 25.0 % | 11 |
FORMULATED TREATS | $6,379,316 | $6,071,751 | $5,327,003 | -12.3 % | 11 |
DENTAL | $1,103,680 | $1,017,407 | $1,022,124 | 0.5 % | 11 |
ACCESSORIES AND TOYS | $229,835 | $211,170 | $195,077 | -7.6 % | 11 |
NATURAL TREATS | $33,044 | $33,044 | $26,584 | -19.5 % | 8 |
WASTE MANAGEMENT & HOUSEBREAK | $31,189 | $28,108 | $11,783 | -58.1 % | 10 |
RAWHIDE | $31,445 | $31,445 | $4,574 | -85.5 % | 11 |
Grand Total | $49,344,155 | $46,547,870 | $49,524,835 | 6.4 % | 11 |
But when I substitute the “11” with a filter and use the MaxMonth measure<o></o>
2016 ADJ NET SALES-YTD =CALCULATE(SUM([ADJUSTED NET SALES]), 'MONTHLYSALES 002'[CAL_YEAR]=2016,filter ('MONTHLYSALES 002', [PERIOD] <= [MAX MONTH]))<o></o>
I get the below results.<o></o>
2016 ADJ NET SALES-TOTAL | 2016 ADJ NET SALES-YTD | 2017 ADJ NET SALES- YTD | YTD % CHNG | MAX MONTH | |
FLEA & TICK | $34,273,033 | $34,273,033 | $34,925,113 | 1.9 % | 11 |
HEALTH AND BEAUTY CARE | $7,262,615 | $7,262,615 | $8,012,576 | 10.3 % | 11 |
FORMULATED TREATS | $6,379,316 | $6,379,316 | $5,327,003 | -16.5 % | 11 |
DENTAL | $1,103,680 | $1,103,680 | $1,022,124 | -7.4 % | 11 |
ACCESSORIES AND TOYS | $229,835 | $229,835 | $195,077 | -15.1 % | 11 |
NATURAL TREATS | $33,044 | $33,044 | $26,584 | -19.5 % | 8 |
WASTE MANAGEMENT & HOUSEBREAK | $31,189 | $31,189 | $11,783 | -62.2 % | 10 |
RAWHIDE | $31,445 | $31,445 | $4,574 | -85.5 % | 11 |
Grand Total | $49,344,155 | $49,344,155 | $49,524,835 | 0.4 % | 11 |
<colgroup><col width="268" style="width: 201pt; mso-width-source: userset; mso-width-alt: 9801;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="4"> <col width="97" style="width: 73pt; mso-width-source: userset; mso-width-alt: 3547;"> <tbody>
</tbody>
<vval id="Oval_x0020_5" style="width: 66.75pt; height: 22.5pt; margin-top: 115.9pt; margin-left: 228.75pt; visibility: visible; position: absolute; z-index: 251659264; mso-wrap-style: square; mso-width-percent: 0; mso-height-percent: 0; mso-wrap-distance-left: 9pt; mso-wrap-distance-top: 0; mso-wrap-distance-right: 9pt; mso-wrap-distance-bottom: 0; mso-position-horizontal: absolute; mso-position-horizontal-relative: text; mso-position-vertical: absolute; mso-position-vertical-relative: text; mso-width-relative: margin; mso-height-relative: margin; v-text-anchor: middle;" filled="f" o:spid="_x0000_s1026" strokeweight="2pt" strokecolor="red" o:gfxdata="UEsDBBQABgAIAAAAIQC75UiUBQEAAB4CAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbKSRvU7DMBSF
dyTewfKKEqcMCKEmHfgZgaE8wMW+SSwc27JvS/v23KTJgkoXFsu+P+c7Ol5vDoMTe0zZBl/LVVlJ
gV4HY31Xy4/tS3EvRSbwBlzwWMsjZrlprq/W22PELHjb51r2RPFBqax7HCCXIaLnThvSAMTP1KkI
+gs6VLdVdad08ISeCho1ZLN+whZ2jsTzgcsnJwldluLxNDiyagkxOquB2Knae/OLUsyEkjenmdzb
mG/YhlRnCWPnb8C898bRJGtQvEOiVxjYhtLOxs8AySiT4JuDystlVV4WPeM6tK3VaILeDZxIOSsu
ti/jidNGNZ3/J08yC1dNv9v8AAAA//8DAFBLAwQUAAYACAAAACEArTA/8cEAAAAyAQAACwAAAF9y
ZWxzLy5yZWxzhI/NCsIwEITvgu8Q9m7TehCRpr2I4FX0AdZk2wbbJGTj39ubi6AgeJtl2G9m6vYx
jeJGka13CqqiBEFOe2Ndr+B03C3WIDihMzh6RwqexNA281l9oBFTfuLBBhaZ4ljBkFLYSMl6oAm5
8IFcdjofJ0z5jL0MqC/Yk1yW5UrGTwY0X0yxNwri3lQgjs+Qk/+zfddZTVuvrxO59CNCmoj3vCwj
MfaUFOjRhrPHaN4Wv0VV5OYgm1p+LW1eAAAA//8DAFBLAwQUAAYACAAAACEAIYg4e74CAABlBgAA
HwAAAGNsaXBib2FyZC9kcmF3aW5ncy9kcmF3aW5nMS54bWysVVtP2zAUfp+0/2D5HdJ2Db2IgLpu
RZMQIALi2ThOE82xPdsN6X79ji9pI4aYtK0Prc853/l8ru75Zddw1DJtaikyPD4dYcQElUUtthl+
fNiczDEyloiCcClYhvfM4MuLjx/OyXKriapqioBBmCXJcGWtWiaJoRVriDmVigmwlVI3xIKot0mh
yQswNzyZjEZnSUNqgS+OVF+IJWin67+g4pJ+Z8WaiJYYoOR0OdTEGDn9d2ayFO2VVrm60y5yetPe
aVQXGYbKCdJAiXASDREGYvLKa3sk6ErdOLwsS9Rl+NN8vEgnKUb7DJ9NFguoU+BjnUUUAPPpbObs
FACTeTpLo51Wt39goNXXdzkgyBAMHAYBGuXCE+3vGUMUIePblnCUHvJ2yD7p3svEev2fdA+hkqXS
xl4x2SB3yDDjvFaG+aki7bWxIZAe5VORm5pz3yQunMJIXhdO5wW9fV5zjSClDG82I/i4xODGAQwk
5+qUMS/b5b5Otvssi70jeoZfGA0tISqYDaPopoYQr4mxd0TDuoASFs/ewlfJ5UuGZTxhVEn98y29
w8MIgxWjF1i/DJsfO6IZRvybMBlejKdToLVemKazCQh6aHkeWsSuWUvIcuyj80eHt7w/llo2T1IX
K3crmIigcHeGqdW9sLYggwm2nLLVyp+pbBSx1yJXsG9j3wpX/4fuiWgV+2Rhnm9kXhH1Zq8CNnRr
tbOyrGMjQ1WdgRub2z1nvpG+9kwUrrL3UHVO3PvFxMljHrsHCGjXsT07w3J1z2jk7ftnHKWnF/es
hMWGlZv4FPyzxg6jQShlwobsTEUKFiYmHQ6Mewidh7+aO0LHXMKkHbgjQY8MJD13mLuId66sLCHi
g/PovcCC88HD3yzF0bmphdRvEXDIKt4c8GHQQ2H8yIPi1QPrIfEPwb3iQ/niFwAAAP//AwBQSwME
FAAGAAgAAAAhAOFRNx/PBgAA5hsAABoAAABjbGlwYm9hcmQvdGhlbWUvdGhlbWUxLnhtbOxZzW/c
RBS/I/E/jHxvs9/NRt1U2c1uA23aKNkW9Thrz9rTjD3WzGzSvaH2iISEKIgDlbhxQEClVuJS/ppA
ERSp/wJvZmyvJ+uQtI2gguaQtZ9/877fm6/LV+7FDB0QISlPel79Ys1DJPF5QJOw590ajy6sekgq
nASY8YT0vDmR3pX199+7jNd8RtMJxyIYRyQmCBglcg33vEipdG1lRfpAxvIiT0kC36ZcxFjBqwhX
AoEPQUDMVhq1WmclxjTx1oGj0oyGDP4lSmqCz8SeZkNQgmOQfnM6pT4x2GC/rhFyLgdMoAPMeh7w
DPjhmNxTHmJYKvjQ82rmz1tZv7yC17JBTJ0wtjRuZP6ycdmAYL9hZIpwUgitj1rdS5sFfwNgahk3
HA4Hw3rBzwCw74OlVpcyz9Zotd7PeZZA9nGZ96DWrrVcfIl/c0nnbr/fb3czXSxTA7KPrSX8aq3T
2mg4eAOy+PYSvtXfGAw6Dt6ALL6zhB9d6nZaLt6AIkaT/SW0DuholHEvIFPOtirhqwBfrWXwBQqy
ocguLWLKE3VSrsX4LhcjAGggw4omSM1TMsU+5OQAxxNBsRaA1wgufbEkXy6RtCwkfUFT1fM+THHi
lSAvn33/8tkTdHT/6dH9n44ePDi6/6Nl5IzawklYHvXi28/+fPQx+uPJNy8eflGNl2X8rz988svP
n1cDoXwW5j3/8vFvTx8//+rT3797WAHfEHhSho9pTCS6QQ7RLo/BMOMVV3MyEa82YhxhWh6xkYQS
J1hLqeA/VJGDvjHHLIuOo0efuB68LaB9VAGvzu46Cu9FYqZoheRrUewAtzlnfS4qvXBNyyq5eTxL
wmrhYlbG7WJ8UCV7gBMnvsNZCn0zT0vH8EFEHDV3GE4UDklCFNLf+D4hFdbdodTx6zb1BZd8qtAd
ivqYVrpkTCdONi0GbdEY4jKvshni7fhm+zbqc1Zl9SY5cJFQFZhVKD8mzHHjVTxTOK5iOcYxKzv8
OlZRlZJ7c+GXcUOpINIhYRwNAyJl1ZibAuwtBf0aho5VGfZtNo9dpFB0v4rndcx5GbnJ9wcRjtMq
7B5NojL2A7kPKYrRDldV8G3uVoh+hzjg5MRw36bECffp3eAWDR2VFgmiv8yEjiW0aqcDxzT5u3bM
KPRjmwPn146hAT7/+lFFZr2tjXgD5qSqStg61n5Pwh1vugMuAvr299xNPEt2CKT58sTzruW+a7ne
f77lnlTPZ220i94KbVevG+yi2CyR4xNXyFPK2J6aM3JdmkWyhHkiGAFRjzM7QVLsmNIIHrO+7uBC
gc0YJLj6iKpoL8IpLLDrnmYSyox1KFHKJWzsDLmSt8bDIl3ZbWFbbxhsP5BYbfPAkpuanO8LCjZm
tgnN5jMX1NQMziqseSljCma/jrC6VurM0upGNdPqHGmFyRDDZdOAWHgTFiAIli3g5Q7sxbVo2Jhg
RgLtdzv35mExUTjPEMkIBySLkbZ7OUZ1E6Q8V8xJAORORYz0Ju8Ur5WkdTXbN5B2liCVxbVOEJdH
702ilGfwIkq6bo+VI0vKxckSdNjzuu1G20M+TnveFPa08BinEHWp13yYhXAa5Cth0/7UYjZVvohm
NzfMLYI6HFNYvy8Z7PSBVEi1iWVkU8N8ylKAJVqS1b/RBreelwE2019Di+YqJMO/pgX40Q0tmU6J
r8rBLlG07+xr1kr5TBGxFwWHaMJmYhdD+HWqgj0BlXA0YTqCfoFzNO1t88ltzlnRlU+vDM7SMUsj
nLVbXaJ5JVu4qeNCB/NWUg9sq9TdGPfqppiSPydTymn8PzNFzydwUtAMdAR8OJQVGOl67XlcqIhD
F0oj6o8ELBxM74BsgbNY+AxJBSfI5leQA/1ra87yMGUNGz61S0MkKMxHKhKE7EBbMtl3CrN6NndZ
lixjZDKqpK5MrdoTckDYWPfAjp7bPRRBqptukrUBgzuef+57VkGTUC9yyvXm9JBi7rU18E+vfGwx
g1FuHzYLmtz/hYoVs6odb4bnc2/ZEP1hscxq5VUBwkpTQTcr+9dU4RWnWtuxlixutHPlIIrLFgOx
WBClcN6D9D+Y/6jwmb1t0BPqmO9Cb0Vw0aCZQdpAVl+wCw+kG6QlTmDhZIk2mTQr69ps6aS9lk/W
57zSLeQec7bW7CzxfkVnF4szV5xTi+fp7MzDjq8t7URXQ2SPlyiQpvlGxgSm6tZpG6doEtZ7Htz8
QKDvwRPcHXlAa2haQ9PgCS6EYLFkb3F6XvaQU+C7pRSYZk5p5phWTmnllHZOgcVZdl+SUzrQqfQV
B1yx6R8P5bcZsILLbj/ypupcza3/BQAA//8DAFBLAwQUAAYACAAAACEAnGZGQbsAAAAkAQAAKgAA
AGNsaXBib2FyZC9kcmF3aW5ncy9fcmVscy9kcmF3aW5nMS54bWwucmVsc4SPzQrCMBCE74LvEPZu
0noQkSa9iNCr1AcIyTYtNj8kUezbG+hFQfCyMLPsN7NN+7IzeWJMk3ccaloBQae8npzhcOsvuyOQ
lKXTcvYOOSyYoBXbTXPFWeZylMYpJFIoLnEYcw4nxpIa0cpEfUBXNoOPVuYio2FBqrs0yPZVdWDx
kwHii0k6zSF2ugbSL6Ek/2f7YZgUnr16WHT5RwTLpRcWoIwGMwdKV2edNS1dgYmGff0m3gAAAP//
AwBQSwECLQAUAAYACAAAACEAu+VIlAUBAAAeAgAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRf
VHlwZXNdLnhtbFBLAQItABQABgAIAAAAIQCtMD/xwQAAADIBAAALAAAAAAAAAAAAAAAAADYBAABf
cmVscy8ucmVsc1BLAQItABQABgAIAAAAIQAhiDh7vgIAAGUGAAAfAAAAAAAAAAAAAAAAACACAABj
bGlwYm9hcmQvZHJhd2luZ3MvZHJhd2luZzEueG1sUEsBAi0AFAAGAAgAAAAhAOFRNx/PBgAA5hsA
ABoAAAAAAAAAAAAAAAAAGwUAAGNsaXBib2FyZC90aGVtZS90aGVtZTEueG1sUEsBAi0AFAAGAAgA
AAAhAJxmRkG7AAAAJAEAACoAAAAAAAAAAAAAAAAAIgwAAGNsaXBib2FyZC9kcmF3aW5ncy9fcmVs
cy9kcmF3aW5nMS54bWwucmVsc1BLBQYAAAAABQAFAGcBAAAlDQAAAAA=
"><v:shape id="Picture_x0020_4" style="width: 468pt; height: 132.75pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1025"> <v:imagedata o:title="" src="file:///C:/Users/arundell/AppData/Local/Temp/msohtmlclip1/01/clip_image004.emz"></v:imagedata></v:shape><o></o></vval>
I want to be able to use the Max Month measure which isautomated, but get the results in the first table. What am I doing wrong?
THANKS FOR THE HELP