cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

LastDate minus 1

I need a filter for (Last Business Day).  We are open Monday through Friday and have hundreds of transaction each day.  One of my sales tabs is "Today's Sales".  I use the "LastDate" formula to display all the sales happening on today's date.

 

I also need a tab titled "Last Business Day Sales".  This way anyone can see what happen the prior business day before those sales get lumped into the "MTD Sales" tab

 

Can I edit the formula below to use the second to last date?  I've tried -1 about everywhere in this formula and it doesn't seem to work.

 

Today's Sales = CALCULATE([Total Sales],LASTDATE(SALES[Ship Date]))

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: LastDate minus 1

Hi Usates,

 

If you have a date table the you should use the DAX function DateAdd

 

or the workaround could be

 

YesterDay Sales = CALCULATE(SUM(Sales[SalesValue]);DATESBETWEEN(Sales[ShipDate];LASTDATE(SALES[ShipDate])-1;LASTDATE(SALES[ShipDate])-1))

 

br

Erik

View solution in original post

3 REPLIES 3
Highlighted

Re: LastDate minus 1

Hi Usates,

 

If you have a date table the you should use the DAX function DateAdd

 

or the workaround could be

 

YesterDay Sales = CALCULATE(SUM(Sales[SalesValue]);DATESBETWEEN(Sales[ShipDate];LASTDATE(SALES[ShipDate])-1;LASTDATE(SALES[ShipDate])-1))

 

br

Erik

View solution in original post

Highlighted
Helper V
Helper V

Re: LastDate minus 1

I have a date table and I'd love to use it but I can't fix the holiday and weekend issue.  On Monday's, I need to display Friday's sales on the "Last Business Day" tab and on July 5th(Tuesday this year), I needed to display Friday's sales on the "Last Business Day" tab since July 4th was on a Monday.  

 

 Does anyone know of a way to incorporate holidays in a date table?

 

 

Thanks for your help on this.

Highlighted

Re: LastDate minus 1

Hi

 

You should consider adding a column in your date table that for each of your dates then states the Last Business day

 

The formula fails because you shouldnt use sum() around your measure [Total Sales]

 

And yes I use semicolons as separator 🙂

 

br

Erik

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Top Solution Authors
Top Kudoed Authors