8 Replies Latest reply on Mar 31, 2018 6:08 AM by Jim Van Sistine

    Max Date in the Year of Most Recent Actuals

    Zach DeMascole

      Hello everyone,

       

      Thank you in advance for your help.

       

      I have a data set that includes both actuals (for past months/years) and forecasts (for future months/years).  There is a column in my data titled "Actuals Indicator" that notes if the given row contains actuals or forecast.

       

      I would like to identify the max date in the data set that falls within the same year as the most recent actuals.  Based on my experiences, I think an LOD formula is the best approach.

       

      I have created the following calculated field to find the most recent actuals:

      {INCLUDE [Actuals Indicator]: MAX(IF [Actuals Indicator] = "Actuals" THEN [Date] END )}

       

      How can I leverage the "Most Recent Actuals" calculation to find the max data that is in the same year?

       

      I have attached a sampled workbook and a simplified data set to help describe the actuals/ forecasts aspect of this question.

       

      In the data set, the Most Recent Actuals is 2/28/2018.  The desired formula would return 12/31/2018 (since that is the max date in the data set that falls within the same year as 2/28/2018).

       

      Thanks,

      Zach

        • 1. Re: Max Date in the Year of Most Recent Actuals
          Michael Ye

          Zach,

           

           

          Since the last day of a year is always 12/31, what you need is to define the year.

           

          Please change the formula to:

           

          {INCLUDE [Actuals Indicator]:MAX

          (MAKEDATE(YEAR((IF[Actuals Indicator]="Actuals" THEN [Date] END )),12,31))}

           

           

          Hope it helps.

           

          • 2. Re: Max Date in the Year of Most Recent Actuals
            Ashish Chaudhari

            Hi Zach,

             

            Actually you don't need to use include and all. As Michael explained is the one way to do it. You can also use the below method were I am not mentioning the additional condition in the formula for the actual.

             

             

            I have used the Date function instead of using MakeDate. Only reason being Makedate is not available when you connect to few sources when connection is live. I would recommend to always use the Date over make date to build the Dates.

             

            Hope you find it helpful.

             

            Regards,

            Ashish C.

            • 3. Re: Max Date in the Year of Most Recent Actuals
              Zach DeMascole

              Ashish Chaudhari

               

              Hello Michael and Ashish,

               

              I think I need to clarify:

              The data I am looking for is the max date in the year that is disreetly in the dataset.

              Based on the sample data I provided, the max date in the year is infact 12/31 since that is the date I used for December-month end reporting, but in reality what often occurs here is that the December month-end for internal accounting purposes is not 12/31, but rather 12/28 or 12/27, etc.

              In that case, I would like the formula to return 12/28/18 or 12/27/18.

               

              Does that make sense?

               

              I apologize. I probably should have framed the sample data differently to illustrate this.

               

              Thanks,

              Zach

              • 4. Re: Max Date in the Year of Most Recent Actuals
                Jim Van Sistine

                Hi Zach,

                Do you mean something like this?  I was able to get to this with 2 separate fixed calcs as shown below.  As you can see, it still worked when I updated the spreadsheet to have 2018 end on 12/28 instead of 12/31.

                 

                LoD-Tab Forums.PNG

                 

                My workbook is attached.  It's in v10.4, so let me know if that's a problem and I'll see if I can save it back to 10.3

                 

                - Jim

                1 of 1 people found this helpful
                • 5. Re: Max Date in the Year of Most Recent Actuals
                  Zach DeMascole

                  Jim Van Sistine; Ashish Chaudhari

                   

                  I used Jim's suggestion and it works, but I notice that he used a different formula to get the most recent actuals, and did not use "INCLUDE".

                   

                  My original reason for using "INCLUDE" was to avoid getting a second result to the formula.  I revisited my formulas and removed "INCLUDE" from the formulas that would still provide only 1 result without it.  For the formulas that still have "INCLUDE", I got a second result when I tried removing "INCLUDE" from it (or from a referencing formula).

                   

                  Does anyone see any issues with the formulas below or have any suggestions for improving them?

                  You can see which formulas still use "INCLUDE".  For Most Recent Actuals, I am using two versions - one with "INCLUDE", one without "INCLUDE".

                   

                  Title: "zzz Most Recent Actuals not using INCLUDE"

                  Formula to find the most recent actuals.

                  {FIXED: MAX( IF [Actuals Indicator] = "Actuals" THEN [Date] END)}

                  When I removed "INCLUDE" from the formula it shifted from a measure to a dimension.  Is that okay?

                   

                  Title: "zzz 2nd Most Recent Actuals using INCLUDE"

                  Formula to find the second most recent actuals.

                  {INCLUDE [Actuals Indicator]: MAX( IF [Actuals Indicator] = "Actuals" and [Date] < {INCLUDE [Actuals Indicator]: MAX ( IF [Actuals Indicator] = "Actuals" THEN [Date] END)} THEN [Date] END)}

                   

                  Title: "zzz Max Date Actuals Year not using INCLUDE"

                  Formula to find the max date discreetly in the data set that is in the same year as the most recent actuals.

                  {FIXED: MAX( IF YEAR ([Date]) = YEAR( [zzz Most Recent Actuals not using INCLUDE]) THEN [Date] END)}

                  When I removed "INCLUDE" from the formula it shifted from a measure to a dimension.  Is that okay?

                   

                  Title: "zzz Prior Year End Actuals - INCLUDE used and with INCLUDE for Most Recent Acts"

                  Formula to find the max date discreetly in the data set that is in the second max year that contains actuals.

                  {INCLUDE [Actuals Indicator]: MAX( IF [Actuals Indicator] = "Actuals" AND YEAR [Date] = YEAR [zzz Most Recent Actuals using INCLUDE] - 1 THEN [Date] END)}

                  I couldn't make this formula without using INCLUDE for Most Recent Actuals (see below)

                   

                            Title: "zzz Most Recent Actuals using INCLUDE"

                            Most Recent Actuals formula specifically for calculating Prior Year End Actuals.

                            {INCLUDE [Actuals Indicator]: MAX( IF [Actuals Indicator] = "Actuals" THEN [Date] END)}

                   

                  I have attached a workbook that incorporates the formulas mentioned above.

                  I have also attached the sample data (modified slightly to better illustrate real scenarios).

                   

                  Thanks everyone for all of your help!!!

                  • 6. Re: Max Date in the Year of Most Recent Actuals
                    Jim Van Sistine

                    Zach DeMascole,

                    Which flavor of LOD you use depends on what you might want to do next, because FIXED and INCLUDE behave differently in Tableau's order of operations.

                     

                    For example, if you were to drag the DATE field onto columns, the include calculation results are blank for all of the future dates.

                    Essentially, these calcs are the same as MAX(DATE) with this grain displayed on the viz.  And that might be just fine.

                     

                    But, if you want the calcs to resolve to values regardless, then you would have to use FIXED.

                     

                    Building from the "Most Recent" date (and year) using fixed, the other 2 dates could be built like this:

                    Preceding Period:

                    {fixed: max(if [Date]< [zzz Most Recent Actuals not using INCLUDE] then [Date] END)}

                     

                    Previous year end:

                    {fixed: MAX(if DATEDIFF('year',[Date],[zzz Max Date Actuals Year not using INCLUDE])=1 then [Date] end)}

                     

                    Doing so moves these calculations earlier in Tableau's Order of Operations, so they resolve correctly for each date in your data set.

                    So, it depends on how you need it to work in the end.  Which "flavor" I chose ultimately depends on my viz, what I need the calcs for and where I need them to happen in the order of ops.  The fixed calcs allow me to move the calculations earlier in the order, which provides options for filtering, etc that include/exclude do not.

                     

                    Hopefully this helps, but let me know if I can clarify any of this further.

                    - Jim

                    2 of 2 people found this helpful
                    • 7. Re: Max Date in the Year of Most Recent Actuals
                      Zach DeMascole

                      Jim,

                      This is extremely helpful information.

                      I am going with the FIXED approach since it doesn't seem like there is any downside.  I am going to keep this thread in mind though if I reach a point when I have a strong preference for INCLUDE or FIXED based on what I am using it for.

                       

                      Really appreciate your help and the formulas you provided.

                       

                      Thank you,

                      Zach

                      • 8. Re: Max Date in the Year of Most Recent Actuals
                        Jim Van Sistine

                        No problem Zach, glad I could help get it working.  Good luck with the rest of your workbook!