# DAX measure : Last year to date, problem with MAX function ... any other possibility ?



## Vander1981 (Jul 13, 2017)

Hi all,

There is something I cannot understand in my model.

I have 2 fact tables.

Table 1 -> last date = MAY17
Table 2 -> last date = JUNE17

and then a calendar table with 2 complete years (16 & 17) : Full date, year, month columns.

I want to make a comparison between these 2 tables. Then I need to use the max date of Table 1 for both tables.

For 17 I have no problem : 

Total Units YTD  =
CALCULATE (
    [Total Units];
    FILTER ( 'Calendar'; 'Calendar'[Date] <= MAX ( Table1[Date] ) )
)

But for Last YTD, I have a problem with the MAX function. Whenever I use DATEADD, SAMEPERIODLASTYEAR,... it gives me the full last year period and I understand why but I cannot find a solution to overcome this. 
How to FIX this MAX (Table1[Date]) and substract one year ???

Thanks a lot


----------



## gazpage (Jul 13, 2017)

I'm not sure I understand what stops your measure from also picking up 2016 values?


----------



## gazpage (Jul 13, 2017)

https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/


----------



## Vander1981 (Jul 13, 2017)

Well, in fact I have the feeling that when I use DATEADD (for example) that it switch indeed from 2017 to 2016 then it looks up for MAX(date) and instead of may16 it takes december16...


----------



## gazpage (Jul 13, 2017)

Can you explain exactly what your output using this measure will look like? What are the filters, slicers, rows, columns etc.


----------



## Vander1981 (Jul 13, 2017)

I have just one filter = 2017 (year)

2 measures = 

Total Units YTD  =
 CALCULATE (
    [Total Units];
    FILTER ( 'Calendar'; 'Calendar'[Date] <= MAX ( Table1[Date] ) )
 )


Total Units LYTD = 
CALCULATE (
    [Total Units YTD];
    DATEADD ( 'Calendar'[Date]; -1; YEAR )
)

The output for 2017 is fine. It takes only JAN17 to MAY17
while the output for 2016 is not what I want : JAN16 to MAY16 but JAN16 to DEC16

I tried everything I could for LYTD without any success....


----------



## gazpage (Jul 13, 2017)

```
Total Units LYTD  =
 CALCULATE (
    [Total Units];
    SAMEPERIODLASTYEAR (
        FILTER ( 'Calendar'; 'Calendar'[Date] <= MAX ( Table1[Date] ) )
    )
 )
```


----------



## Vander1981 (Jul 13, 2017)

Thanks Gazpage ! 
I just had to adapt a bit the measure because it didn't work to put a table with multiple columns in SAMEPERIODLASTYEAR function.



```
Total Unit LYTD =
CALCULATE (
    [Total Units];
    SAMEPERIODLASTYEAR (
        FILTER (
            VALUES ( 'Calendar'[Date] );
            'Calendar'[Date] <= MAX ( Table1[Date] )
        )
    )
)
```


----------



## gazpage (Jul 13, 2017)

Right, sorry was in a rush earlier.

In case you care, your original measure didn't work because the CALCULATE alters the filter context and then the inner measure is calculated. So you started with 2017 due to your filter, shifted that back to 2016, before finally your measure further filtered it to be before May 2017. Obviously all dates in 2016 are before 2017 so it just stayed as all of 2016.


----------

