Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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 displ...
Need Help
  • donsvensen's avatar
    donsvensen
    10 years ago

    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

donsvensen's avatar
donsvensen
Icon for Skilled Sharer rankSkilled Sharer
10 years ago

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's avatar
    Anonymous
    Not applicable
    10 years ago

    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.

    • donsvensen's avatar
      donsvensen
      Icon for Skilled Sharer rankSkilled Sharer
      10 years ago

      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