Guest Post: Always Show Yesterday, Today, or Tomorrow’s Data

 
Today we have a new guest poster – Miguel Escobar.  I’ve been talking to Miguel in email and Skype for a long time now and feel silly that I haven’t asked him to do a guest post until today.  But now, I have, so I can stop feeling silly.

From his writing style and creative approach to solving problems, I think you’ll see that he fits right in.

Cool trick: Always show Yesterday’s, Today’s or Tomorrow’s Data

clip_image002

Executive: Are these values correct?
Excel-guy: yes, but you need to check the dates slicers to see what dates the report is using
Executive: Ugh… I just want to click on the report and see the latest values

If you ever had this situation before let me tell you that you’re not alone on that one…I’ve been there before and it’s time to give you some cool easy tricks on how to set up a Powerpivot report that shows you the yesterday, todays, tomorrow, next week or any type of timeframe (forecasting or that sort of scenario).


What could we do?

First, let’s analyze the situation and the possible solutions that we could provide:

1. Teach the Executive to use slicers and how-to play with them (show him how fun that is!)

a. You should always try to strive towards making the users feel more comfortable with the tools that they are using. But perhaps we could make things easier for them?

2. Drag the DATES to the rows or columns and use the dates filtering option

a. Probably the best option, but for this example let’s say that we don’t want to touch the aesthetics of the report or any type of layout in terms of the actual rows/columns and value fields

3. Create a DAX measure aka calculated field

a. This could also be a great solution but it depends on how you want things to be calculated…we’ll talk about it later on

4. Create a calculated column

a. This will require you to add a column to your calendar table but it also depends on how you want things to be calculated

The solution!

clip_image004

The image above shows what the price for gas is today all over the world… (or what my eyes distinguish as prices)

Using the Dates as filters inside the pivot table

All you have to do is drag the DATES column into either the rows or columns, go to the filter section of it and then select the DATE FILTERS option to filter it as you need.

clip_image006  clip_image008

Excel Desktop Image on Left, SharePoint 2013 Environment on Right

Using TODAY() and NOW() – volatile functions

Using TODAY() will evaluate into Today’s Date and the NOW() function will evaluate into the date and time but…here’s the main question, when are those values being calculated?

When calculated inside the Powerpivot Grid (the powerpivot window)

It could be calculated automatically:

- Everytime you do a refresh for the data model or create a calculated column or field aka measure

Or it could be manually going through the

Design Tab > Calculation Options > Manual Calculation Mode

clip_image010

And then hitting the CALCULATE NOW button any time you need to calculate the values

When calculated as a measure or calc field

The exact moment that this value is calculated it’s when you refresh the pivot tables.

This concepts are necessary when you’re creating the solution as you’ll need to take in consideration these 2 questions:

1. Do I want the TODAY’S or NOW’s date/time value to match the one for when my data model was last refreshed?

2. Do I need to always have the latest date & time being calculated at the pivot table refreshing time?

It’s up to you to decide how to set-up the solution but after you get that part done, we can go ahead and create the solution!

We now know what we want to show in our reports, let’s go for the formula time!

DAX Measure aka Calc Field

The DAX measure or calc field approach would look something like this

=CALCULATE(SUM(FactTab[Sales]),
   FILTER(CalendarTable,
          CalendarTable[Dates] = TODAY()
         )
)

You could also make a variation on this and get current year or current month changing the column of the calendar table to Month# or Year# and using something like

MONTH(TODAY())

or

YEAR(TODAY())

Calculated Column (Powerpivot Grid)

Using your Calendar Table you can use a new table like the following:

Category

Diff

Yesterday

-1

Today

0

Tomorrow

1

Where on the category you get the name that will appear in the slicers and on the column “Diff” the difference of days against TODAY()’s value. Once you have that table added to the data model you can then create a calc column on the calendar table using a formula like this:

= [Dates] – TODAY()

clip_image012

Once you have that covered you can now link that [Difference in days] calc column to the one on the table that we just added using the column Diff.

To finish, just add the slicer to your report and let the executive know that the slicer is now ready for its use.

Don’t forget to watch some powerpivot video tricks here

http://www.youtube.com/poweruserxl

or check out my blog here

https://thepoweruser.wordpress.com/

Miguel Escobar, aka The Power User

14 Responses to Guest Post: Always Show Yesterday, Today, or Tomorrow’s Data

  1. Donald Parish says:

    Interesting…I just experimented with Chris Webb’s Building Relative Date Reports in PowerPivot this week: http://cwebbbi.wordpress.com/2013/01/24/building-relative-date-reports-in-powerpivot/

  2. it’s a pretty nifty approach he has there! I believe that the combination of TODAY or NOW with other functions can be greatly used. One of the topics that I didn’t cover was that you could also use a formula like the calculate that I have up there and then use a filter to your calendar table to get something like your current Quarter using RELATED or FORMAT( TODAY(), “Q”) or current week.

    hope you find the post useful!
    Miguel-

  3. Mahmoud Fouz says:

    Thanks for the post! On a related note, how can I then compute a column showing the relative change between different dates? Say I want to display for each measure the week on week change.

  4. Using the [Difference in days] column you could do something like what Chris Webb did or you could have another disconnected table where you can define the ranges of the week. For example
    Column1 will have the name or description (last week), the 2nd column will have the lowest value in dates difference in this case it could be -6 and then in the max value you could have 0.

    More info on the banding here
    http://sqlblog.com/blogs/alberto_ferrari/archive/2010/10/12/banding-with-powerpivot.aspx

    and a video of the banding here
    https://www.youtube.com/watch?v=AfnCiLXHWzU

    Let me know if this helps

  5. Mahmoud Fouz says:

    Not sure whether the request was clear. Say I’m tracking sales over weeks. Now for each week, I want to display the relative change in sales compared to the previous week. Does your solution address this problem?

  6. Frank Tonsen says:

    My slicer does not only show Yesterday, Today and Tomorrow but (blank) as well …
    How did you avoid that?

    • Unfortunately, that’s one of the downsides of creating this type of alternative solutions. Excel needs to group all of the other dates that are not yesterday, today or tomorrow into a single group and since we didn’t define one they are all being grouped into the “blank” group. It makes sense, but you could also try another approach:

      http://www.ssas-info.com/VidasMatelisBlog/253_portfolio-slicer-free-bi-application-to-track-your-investments-in-excel-with-powerpivot

      Check out the workbook there and you’ll notice that Vidas created several calculated columns with true/false to show if the date in question is within the range that the column defines. Any other approach will leave you with some blanks or too many values for the slicers to become a viable solution for the top-executive ;)

      • Frank Tonsen says:

        Then, for a top-executive, I would rather create a disconnected table to populate the slicer perfectly.

        • I agree with you. In the end, it depends on how you want things to show up for the end-user. The blank option on the slicers is probably something that could compromise the solution but if it’s something that has a meaning, in other words, that can be explained and makes sense to the end user then it’s something that you could work with until Microsoft actually does something about it.

          More than anything, this example it’s quite critical to users that like to play with Power View, since Power View does not have the option for the DATE filters at all and they do not want to add several TRUE/FALSE filters to their Power View reports.

  7. John Bradley says:

    Hey Miguel,

    Great post. I have a report I am using for our leadership team that goes and skips the current two weeks and then selects the previous six weeks. Any idea how I could auto select a particular time frame like that. I know that weeks are generally more difficult to work with but hoping that you have some idea about how this can be done. Love the concept. Hope I can apply to my particular brand situation.

    Kind Regards,

    John Bradley

Leave a Comment or Question