3 Replies Latest reply on Apr 30, 2017 3:34 AM by Julia Balkheimer

# Get last date and corresponding value

Hi everyone,

I am able to get th elast day from different dates, however I have issues of receiving the value of another variable for the last date.

I am calculating the last date this way:

Latest date:

TOTAL(MAX([Col34]))

As a last step I would like  to get the value of the KPI in "col35" for the last day

LOOKUP(WINDOW_SUM(sum(IIF([Latest Date],[Col35],0))),0)

I would like to the same for YTD and MTD.

How do I calculate YTD, MTD?

Thanks

Julia

• ###### 1. Re: Get last date and corresponding value

Hi Julia,

Not sure but find my approach below

YTD: if datediff('year', [Col34],today())=0 and {fixed [Col34 (Years)]: max([Col34])}=[Col34] then [Col35] end

MTD: if datediff('month', [Col34],today())=0 and {fixed [Col34 (Months)]: max([Col34])}=[Col34] then [Col35] end

Regards,

Norbert

• ###### 2. Re: Get last date and corresponding value

Hi

See the attached workbook -

The key calculations are - last date { FIXED [Name1]:(MAX([Col34])) }

Then the volume on the last day >{ FIXED [Name1]: sum(if [Col34] = [Latest Date] then [Col35] else 0 end)}

that combination will yield a single value for the last day volume by Name 1

I'm not certain what you do with that but at least you have the value

Let me know if that help[s

Jim

• ###### 3. Re: Get last date and corresponding value

Hi Nobert,

I am receiving an error for the YTD and MTD calculation.

if datediff('year', [Col34],today())=0 and {fixed [Col34 (Years)]: max([Col34])}=[Col34] then [Col35] end

Tableau underlines " Col34 (Years)" and says: " reference to undefined field"

Any idea why?

Did thanks

Julia