Skip to main content
Topics#Tableau Desktop & Web AuthoringTableau Community Forums Member (Inactive)'s Question
Tableau Community Forums Member (Inactive) (Salesforce) asked in #Tableau Desktop & Web Authoring
Oct 17, 2023, 2:31 PM

How can I calculate for a sum of values 11 months ago?

I have an ask by one of our end users which requires me to sum values for revenue multiple months ago. However, I am running into an issue with not calculating the appropriate amount. Current state when I am asking this question we are in October, and when I query for the revenue 11 months ago I receive a null value. I'm using the following formulas:

  • To identify the month 11 months ago: DATEADD('month',-11,today())
  • To identify if the sale was 11 monthsw ago: DATETRUNC('month', [11 Months Ago]) = DATETRUNC('month',[EffectiveDate]) AND (DATETRUNC('year',[EffectiveDate]) = [This Year] OR DATETRUNC('year',[EffectiveDate]) = [One Year Ago])
  • To sum the revenue from 11 months ago: IF [Sale 11 Months Ago] = true then [Order Amount After Discount] ELSE null END

 

I can't figure out how to get an anonymous version of my workbook, but here is the need: I'm trying to get to the sales for whatever month we are in now to two years ago only, but the formulas aren't working. When I had the second formula above set to only be the first part of the formula I was getting every sale in the month 11 months ago (for example, if November was 11 months ago, then I would get every sale in November regardless of year rather than just the Sales in November of last year)

1 answer
  1. Jim Dehner - Tableau Visionary HoF (Market Analytics LLC)
    Oct 17, 2023, 2:39 PM

    Good morning @Kiersten Patton​ 

     

    in this  DATETRUNC('month', [11 Months Ago]) = DATETRUNC('month',[EffectiveDate]) AND (DATETRUNC('year',[EffectiveDate]) = [This Year] OR DATETRUNC('year',[EffectiveDate]) = [One Year Ago])

     

    you dont need the second clause (it is causing the problem ) you can combine the 2 statements into one datetrunc('month',effectivedate) =datetrunc('month', dateadd('month',-11,effectivedate)) with be true for 11 ponths prior to the effective data

     

    BUT if you need month to date you would need a second statement and rather than writing it here I refer you to https://jimdehner.com/2021/08/22/5-ways-to-do-yoy-and-ytd/ it is not your problem but a model that you can use to create your own to data type calculation

    when you have a tableau workbook ready (fake data is fine) then post what you have done as a twbx

Write an answer...
0/9000