Forum Discussion

PBIn00b's avatar
PBIn00b
Icon for Advocate II rankAdvocate II
10 years ago

PREVIOUSDAY isn't working... help?

I am attempting to get a previous day's value.  Unfortunately it doesn't seem to be working for me.  Here is the formula I am using:   Previous Day Sales = CALCULATE(SUM('table'order_total]),PREVIO...
Anonymous's avatar
Anonymous
Not applicable
10 years ago

Hey PBIn00b -

 

Are you using a a calendar table?  It's best practice to use a calendar table with a continuous range of dates.  That way even if your business doesn't run 24/7, your date table has 1 row for EVERY day in the year(s).

 

 

There are many resources to create a standard or custom calendar.  I would recommend looking at PowerPivotPro's post here and Matt Allington's post here.

 

Once you create a calendar table, you can create a relationship between the [CalendarDate] and 'table'[ship_date].  Power BI will take care of the rest.

 

My thought is that for your given filter context, there is no data with a ship date that's 1 day earlier?  Can't confirm without seeing your data.

 

Hope this helps!

~ Chris

 

  • PBIn00b's avatar
    PBIn00b
    Icon for Advocate II rankAdvocate II
    10 years ago

    Hi ChrisHaas, thanks for your reply.

     

    I do have a calendar table.  There is a relationship between my ship date and my calendar table.

     

    I checked the raw data and there are definitely orders from yesterday.

     

    Also, I created another formula:

     

    Yesterday = RELATED('ordertable'[Yesterday Sales])*'itemtable'[SKU Qty]

     

    In the order table I set up a reference column that gives a 1 or a 0 if the date is yesterday. Then I just ask the formula to multiply that by the number of items sold. So 1 * items sold or 0 * items sold.  I know there are other ways, but I wanted a very direct / basic visual of what was working or not.  In this case I get data from yesterday.

     

    I also feel like if it was a problem with my calendar table the MTD and YTD functions wouldn't work properly.

    • v-sihou-msft's avatar
      v-sihou-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      PBIn00b

       

      As I tested, when we directly import a date column into table visual, and include a calculated measure with PREVIOUSDAY() function, I reproduced same issue as yours. The measure return all BLANK().

       

      However, I find if we build a date hierarchy with the date column and import the data field under hierarchy, the measure works properly.

       

       

      I would recommend you sumbit this issue as potential bug on Idea: https://ideas.powerbi.com/forums/265200-power-bi. We will also report this issue internally.