Showing results for 
Search instead for 
Did you mean: 
PBIn00b Regular Visitor
Regular Visitor

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]),PREVIOUSDAY('table'[ship_date]))


When I attempt to put it in the report I get (Blank).  I know there is data in both the date and the order total fields for the previous day.  I can get TOTALYTD and TOTALMTD to work fine using the following so the dates seem fine.


YTD Total Sales = TOTALYTD(SUM('table'[order_total]),'table'[ship_date])


Any ideas what I am doing wrong?

ChrisHaas Established Member
Established Member

Re: PREVIOUSDAY isn't working... help?

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 Regular Visitor
Regular Visitor

Re: PREVIOUSDAY isn't working... help?

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.

Moderator v-sihou-msft

Re: PREVIOUSDAY isn't working... help?



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: We will also report this issue internally.

Vicky_Song Established Member
Established Member

Re: PREVIOUSDAY isn't working... help?

@PBIn00b, thanks for reporting it. Your issue can be reproduced and I reported it internally. I will post back once I get any feedback. 

Vicky_Song Established Member
Established Member

Re: PREVIOUSDAY isn't working... help?

@PBIn00b, with the Previous Day Sales = CALCULATE(SUM('table'order_total]),PREVIOUSDAY('table'[ship_date])) formula, set field under Values from "Date Hierarchy" to "DueDate", then data shows up correctly. 


Or, you can have the DAX to be similar to: 

CALCULATE(SUM('Purchasing PurchaseOrderDetail'[UnitPrice]),PREVIOUSDAY('Purchasing PurchaseOrderDetail'[DueDate].[Date]))


Leila Frequent Visitor
Frequent Visitor

Re: PREVIOUSDAY isn't working... help?

I have the same problem, while niether PREVIOUSDAY nor DATEADD functions work when I want to calculate the SUM(sessions) for a previous period. ( I need this calculation to know the growth rate for day over day


I have searched the whole powerbi community in the last three days, and much to my surprise, it is still unresolved Smiley Sad


This is my formula : PreDaySessions = CALCULATE(SUM(Visitor[SessionS]), DATEADD(Visitor[Date].[Date], -1 , DAY))


Thanks in andvance,