DAX Max Month Measure

[andrewr1]

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>
[TABLE="width: 582"]
<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>[TR]
[TD="class: xl68, width: 268"][/TD]
[TD="class: xl69, width: 103"]2016 ADJ NET SALES-TOTAL[/TD]
[TD="class: xl69, width: 103"]2016 ADJ NET SALES-YTD[/TD]
[TD="class: xl69, width: 103"]2017 ADJ NET SALES- YTD[/TD]
[TD="class: xl70, width: 103"]YTD % CHNG[/TD]
[TD="width: 97"]MAX MONTH[/TD]
[/TR]
[TR]
[TD="class: xl71"]FLEA & TICK[/TD]
[TD="class: xl65, align: right"]$34,273,033[/TD]
[TD="class: xl65, align: right"]$32,747,351[/TD]
[TD="class: xl67, align: right"]$34,925,113[/TD]
[TD="class: xl66, align: right"]6.7 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]HEALTH AND BEAUTY CARE[/TD]
[TD="class: xl65, align: right"]$7,262,615[/TD]
[TD="class: xl65, align: right"]$6,407,594[/TD]
[TD="class: xl67, align: right"]$8,012,576[/TD]
[TD="class: xl66, align: right"]25.0 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]FORMULATED TREATS[/TD]
[TD="class: xl65, align: right"]$6,379,316[/TD]
[TD="class: xl65, align: right"]$6,071,751[/TD]
[TD="class: xl67, align: right"]$5,327,003[/TD]
[TD="class: xl66, align: right"]-12.3 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]DENTAL[/TD]
[TD="class: xl65, align: right"]$1,103,680[/TD]
[TD="class: xl65, align: right"]$1,017,407[/TD]
[TD="class: xl67, align: right"]$1,022,124[/TD]
[TD="class: xl66, align: right"]0.5 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]ACCESSORIES AND TOYS[/TD]
[TD="class: xl65, align: right"]$229,835[/TD]
[TD="class: xl65, align: right"]$211,170[/TD]
[TD="class: xl67, align: right"]$195,077[/TD]
[TD="class: xl66, align: right"]-7.6 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]NATURAL TREATS[/TD]
[TD="class: xl65, align: right"]$33,044[/TD]
[TD="class: xl65, align: right"]$33,044[/TD]
[TD="class: xl67, align: right"]$26,584[/TD]
[TD="class: xl66, align: right"]-19.5 %[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl71"]WASTE MANAGEMENT & HOUSEBREAK[/TD]
[TD="class: xl65, align: right"]$31,189[/TD]
[TD="class: xl65, align: right"]$28,108[/TD]
[TD="class: xl67, align: right"]$11,783[/TD]
[TD="class: xl66, align: right"]-58.1 %[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl71"]RAWHIDE[/TD]
[TD="class: xl65, align: right"]$31,445[/TD]
[TD="class: xl65, align: right"]$31,445[/TD]
[TD="class: xl67, align: right"]$4,574[/TD]
[TD="class: xl66, align: right"]-85.5 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]Grand Total[/TD]
[TD="class: xl65, align: right"]$49,344,155[/TD]
[TD="class: xl65, align: right"]$46,547,870[/TD]
[TD="class: xl67, align: right"]$49,524,835[/TD]
[TD="class: xl66, align: right"]6.4 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_3" style="width: 468pt; height: 132.75pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1026"> <v:imagedata o:title="" src="file:///C:/Users/arundell/AppData/Local/Temp/msohtmlclip1/01/clip_image001.emz"></v:imagedata></v:shape>


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>
[TABLE="width: 582"]
<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>[TR]
[TD="class: xl68, width: 268"][/TD]
[TD="class: xl69, width: 103"]2016 ADJ NET SALES-TOTAL[/TD]
[TD="class: xl69, width: 103"]2016 ADJ NET SALES-YTD[/TD]
[TD="class: xl69, width: 103"]2017 ADJ NET SALES- YTD[/TD]
[TD="class: xl70, width: 103"]YTD % CHNG[/TD]
[TD="width: 97"]MAX MONTH[/TD]
[/TR]
[TR]
[TD="class: xl71"]FLEA & TICK[/TD]
[TD="class: xl65, align: right"]$34,273,033[/TD]
[TD="class: xl65, align: right"]$34,273,033[/TD]
[TD="class: xl67, align: right"]$34,925,113[/TD]
[TD="class: xl66, align: right"]1.9 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]HEALTH AND BEAUTY CARE[/TD]
[TD="class: xl65, align: right"]$7,262,615[/TD]
[TD="class: xl65, align: right"]$7,262,615[/TD]
[TD="class: xl67, align: right"]$8,012,576[/TD]
[TD="class: xl66, align: right"]10.3 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]FORMULATED TREATS[/TD]
[TD="class: xl65, align: right"]$6,379,316[/TD]
[TD="class: xl65, align: right"]$6,379,316[/TD]
[TD="class: xl67, align: right"]$5,327,003[/TD]
[TD="class: xl66, align: right"]-16.5 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]DENTAL[/TD]
[TD="class: xl65, align: right"]$1,103,680[/TD]
[TD="class: xl65, align: right"]$1,103,680[/TD]
[TD="class: xl67, align: right"]$1,022,124[/TD]
[TD="class: xl66, align: right"]-7.4 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]ACCESSORIES AND TOYS[/TD]
[TD="class: xl65, align: right"]$229,835[/TD]
[TD="class: xl65, align: right"]$229,835[/TD]
[TD="class: xl67, align: right"]$195,077[/TD]
[TD="class: xl66, align: right"]-15.1 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]NATURAL TREATS[/TD]
[TD="class: xl65, align: right"]$33,044[/TD]
[TD="class: xl65, align: right"]$33,044[/TD]
[TD="class: xl67, align: right"]$26,584[/TD]
[TD="class: xl66, align: right"]-19.5 %[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl71"]WASTE MANAGEMENT & HOUSEBREAK[/TD]
[TD="class: xl65, align: right"]$31,189[/TD]
[TD="class: xl65, align: right"]$31,189[/TD]
[TD="class: xl67, align: right"]$11,783[/TD]
[TD="class: xl66, align: right"]-62.2 %[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl71"]RAWHIDE[/TD]
[TD="class: xl65, align: right"]$31,445[/TD]
[TD="class: xl65, align: right"]$31,445[/TD]
[TD="class: xl67, align: right"]$4,574[/TD]
[TD="class: xl66, align: right"]-85.5 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: xl71"]Grand Total[/TD]
[TD="class: xl65, align: right"]$49,344,155[/TD]
[TD="class: xl65, align: right"]$49,344,155[/TD]
[TD="class: xl67, align: right"]$49,524,835[/TD]
[TD="class: xl66, align: right"]0.4 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]

<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

 

[gazpage]

Jesus! My eyes!

This is really hard to follow, but you probably want to replace [Max Month] in your last measure with the actual text of the measure, to avoid context transition.

 

[andrewr1]

Thanks gaz,
I agree that is brutal. I am not sure what happened with the post. I tried to paste a excel table that must have screwed it up.

Your suggestion worked. So that I can learn. What do you mean by context transition??

 

[andrewr1]

To make it easier, I cleaned up the post.

I am new to this DAX language and I am sure the solution is a very simple one, but I would appreciate some help.

I have created two measures.

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>
[TABLE="width: 582"]
<tbody>[TR]
[TD][/TD]
[TD]2016 ADJ NET SALES-TOTAL
[/TD]
[TD]2016 ADJ NET SALES-YTD
[/TD]
[TD]2017 ADJ NET SALES- YTD
[/TD]
[TD]YTD % CHNG
[/TD]
[TD]MAX MONTH
[/TD]
[/TR]
[TR]
[TD]FLEA & TICK
[/TD]
[TD="align: right"]$34,273,033
[/TD]
[TD="align: right"]$32,747,351
[/TD]
[TD="align: right"]$34,925,113
[/TD]
[TD="align: right"]6.7 %
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]HEALTH AND BEAUTY CARE
[/TD]
[TD="align: right"]$7,262,615
[/TD]
[TD="align: right"]$6,407,594
[/TD]
[TD="align: right"]$8,012,576
[/TD]
[TD="align: right"]25.0 %
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]FORMULATED TREATS
[/TD]
[TD="align: right"]$6,379,316
[/TD]
[TD="align: right"]$6,071,751
[/TD]
[TD="align: right"]$5,327,003
[/TD]
[TD="align: right"]-12.3 %
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]DENTAL
[/TD]
[TD="align: right"]$1,103,680
[/TD]
[TD="align: right"]$1,017,407
[/TD]
[TD="align: right"]$1,022,124
[/TD]
[TD="align: right"]0.5 %
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]ACCESSORIES AND TOYS
[/TD]
[TD="align: right"]$229,835
[/TD]
[TD="align: right"]$211,170
[/TD]
[TD="align: right"]$195,077
[/TD]
[TD="align: right"]-7.6 %
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]NATURAL TREATS
[/TD]
[TD="align: right"]$33,044
[/TD]
[TD="align: right"]$33,044
[/TD]
[TD="align: right"]$26,584
[/TD]
[TD="align: right"]-19.5 %
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TD]WASTE MANAGEMENT & HOUSEBREAK
[/TD]
[TD="align: right"]$31,189
[/TD]
[TD="align: right"]$28,108
[/TD]
[TD="align: right"]$11,783
[/TD]
[TD="align: right"]-58.1 %
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]RAWHIDE
[/TD]
[TD="align: right"]$31,445
[/TD]
[TD="align: right"]$31,445
[/TD]
[TD="align: right"]$4,574
[/TD]
[TD="align: right"]-85.5 %
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD="align: right"]$49,344,155
[/TD]
[TD="align: right"]$46,547,870
[/TD]
[TD="align: right"]$49,524,835
[/TD]
[TD="align: right"]6.4 %
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
</tbody>[/TABLE]

<v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"><v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas><vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"> <o:lock v:ext="edit" aspectratio="t"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_3" style="width: 468pt; height: 132.75pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1026"> <v:imagedata o:title="" src="file:///C:/Users/arundell/AppData/Local/Temp/msohtmlclip1/01/clip_image001.emz"></v:imagedata></v:shape>


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.
[TABLE="class: cms_table, width: 582"]
<tbody>[TR]
[TD="class: cms_table_xl68, width: 268"][/TD]
[TD="class: cms_table_xl69, width: 103"]2016 ADJ NET SALES-TOTAL[/TD]
[TD="class: cms_table_xl69, width: 103"]2016 ADJ NET SALES-YTD[/TD]
[TD="class: cms_table_xl69, width: 103"]2017 ADJ NET SALES- YTD[/TD]
[TD="class: cms_table_xl70, width: 103"]YTD % CHNG[/TD]
[TD="width: 97"]MAX MONTH[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]FLEA & TICK[/TD]
[TD="class: cms_table_xl65, align: right"]$34,273,033[/TD]
[TD="class: cms_table_xl65, align: right"]$34,273,033[/TD]
[TD="class: cms_table_xl67, align: right"]$34,925,113[/TD]
[TD="class: cms_table_xl66, align: right"]1.9 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]HEALTH AND BEAUTY CARE[/TD]
[TD="class: cms_table_xl65, align: right"]$7,262,615[/TD]
[TD="class: cms_table_xl65, align: right"]$7,262,615[/TD]
[TD="class: cms_table_xl67, align: right"]$8,012,576[/TD]
[TD="class: cms_table_xl66, align: right"]10.3 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]FORMULATED TREATS[/TD]
[TD="class: cms_table_xl65, align: right"]$6,379,316[/TD]
[TD="class: cms_table_xl65, align: right"]$6,379,316[/TD]
[TD="class: cms_table_xl67, align: right"]$5,327,003[/TD]
[TD="class: cms_table_xl66, align: right"]-16.5 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]DENTAL[/TD]
[TD="class: cms_table_xl65, align: right"]$1,103,680[/TD]
[TD="class: cms_table_xl65, align: right"]$1,103,680[/TD]
[TD="class: cms_table_xl67, align: right"]$1,022,124[/TD]
[TD="class: cms_table_xl66, align: right"]-7.4 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]ACCESSORIES AND TOYS[/TD]
[TD="class: cms_table_xl65, align: right"]$229,835[/TD]
[TD="class: cms_table_xl65, align: right"]$229,835[/TD]
[TD="class: cms_table_xl67, align: right"]$195,077[/TD]
[TD="class: cms_table_xl66, align: right"]-15.1 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]NATURAL TREATS[/TD]
[TD="class: cms_table_xl65, align: right"]$33,044[/TD]
[TD="class: cms_table_xl65, align: right"]$33,044[/TD]
[TD="class: cms_table_xl67, align: right"]$26,584[/TD]
[TD="class: cms_table_xl66, align: right"]-19.5 %[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]WASTE MANAGEMENT & HOUSEBREAK[/TD]
[TD="class: cms_table_xl65, align: right"]$31,189[/TD]
[TD="class: cms_table_xl65, align: right"]$31,189[/TD]
[TD="class: cms_table_xl67, align: right"]$11,783[/TD]
[TD="class: cms_table_xl66, align: right"]-62.2 %[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]RAWHIDE[/TD]
[TD="class: cms_table_xl65, align: right"]$31,445[/TD]
[TD="class: cms_table_xl65, align: right"]$31,445[/TD]
[TD="class: cms_table_xl67, align: right"]$4,574[/TD]
[TD="class: cms_table_xl66, align: right"]-85.5 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl71"]Grand Total[/TD]
[TD="class: cms_table_xl65, align: right"]$49,344,155[/TD]
[TD="class: cms_table_xl65, align: right"]$49,344,155[/TD]
[TD="class: cms_table_xl67, align: right"]$49,524,835[/TD]
[TD="class: cms_table_xl66, align: right"]0.4 %[/TD]
[TD="align: right"]11[/TD]
[/TR]
</tbody>[/TABLE]


The measure for 2016 Adj Net Sales YTD = 2016 Adj Net Sales Total


I want to be able to use the Max Month measure which is automated, but get the results in the first table. What am I doing wrong?

THANKS FOR THE HELP

 

[gazpage]

This might be a bigger topic than I can can veer in a short forum post from my phone...

If we just look at the filter part as that’s where the problem is.

filter ('MONTHLYSALES 002', [PERIOD] <= [MAX MONTH] )

Naively we might expect this to return all the rows in MonthlySales 002 Table where the date is less than the largest date selected in the filter (NB, I believe PERIOD is a column, always use the table name in such cases so it is clear which is a column and which is a measure). How it actually works is as follows:

1. Iterate through every row in the table that are not filtered out by your current filters.
2. Check to see if the criteria in the second clause ie PERIOD < MAX DATE and if not, exclude the row.
2a. If MAX DATE is written out in full then, as MAX is an aggregator and returns the maximum value in the filter context, rows are excluded if their dates are above the maximum of the filters then exclude (this is the desired result).
2b. If the MAX DATE measure is used, then context transition is carried out automatically (because it is a measure). Any existing row context is converted to a filter context. The row context is the row that is currently being iterated and it is converted to a filter context; the Max Date therefore returns the max date of the current row only. Clearly the date for every row is less than or equal to the max of itself so gets included.