cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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

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

Anonymous
Not applicable

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.

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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors