Dax Max Month

Anything related to PowerPivot and DAX Formuale
andrewr19
Posts: 1
Joined: Thu Dec 28, 2017 6:55 am

Dax Max Month

Postby andrewr19 » Thu Dec 28, 2017 7:03 am

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]), 'MONTHLYSALES 002'[CAL_YEAR]=2016,'MONTHLYSALES 002'[PERIOD]<=11)

Which gives me the desired results in a Pivot table
Table 1.png
Table 1.png (20.21 KiB) Viewed 211 times



But when I substitute the “11” with a filter and use the Max Month measure
2016 ADJ NET SALES-YTD =CALCULATE(SUM([ADJUSTED NET SALES]), 'MONTHLYSALES 002'[CAL_YEAR]=2016,filter ('MONTHLYSALES 002', [PERIOD] <= [MAX MONTH]))

I get the below results in Tabel 2
Table 2.png
Table 2.png (18.8 KiB) Viewed 211 times



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?

MattAllington
Posts: 919
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Dax Max Month

Postby MattAllington » Thu Dec 28, 2017 7:13 am

If you want to do time intelligence calcs (and let's face it - who doesn't), you really should get a calendar table. Read my articles here

https://exceleratorbi.com.au/power-pivo ... ar-tables/
https://exceleratorbi.com.au/dax-time-i ... beginners/
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest

cron