Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Last weekday Dax formula

I have a sales report that I report. On Monday's, I want the report to show sales for Friday, not Sunday. We are close Saturday and Sunday. So my simple sales for now()-1 does not work. Please help
Need Help

5 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    Hi Usates,

    To show the sales for last weekday, I'm using a measure with the following expression.

    LastWeekDaySales = CALCULATE(SUM(salestable[sales]),salestable[ship_date]=SWITCH(WEEKDAY(TODAY(),2),1,TODAY()-3,7,TODAY()-2,TODAY()-1))


    Then I drag the mesure into the report and see the sales only for last weekday.

    • kcantor's avatar
      kcantor
      Icon for Community Champion rankCommunity Champion
      10 years ago

      If you are only looking at the current year or just comparing same period last year, you can also add a field to your date table to include the day of the year. Then, add that as a filter and just manually select which day you want to end the report on. For example, if you were reporting through Friday, March 25, 2016, you would filter day of the year as being less than 86 so that it would pull in numbers through day 85 which was Friday.

      I actually have this in one of my reports because of specific data requests that may change over the course of any given week. It allows me to pin down to a specific day without recreating the switch formula.

      You absolutely do need a date table, however.

      • GTR's avatar
        GTR
        Icon for Helper III rankHelper III
        10 years ago

        You can easily get around all this trouble by having a weekday/weekend indicator in a Date table instead of having to use a formula to decide what the current/last date is. This way, you can filter on the data using this field. 

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    Can't do much without the data you have, are you using a date table? One way would be to create a new column that would be something like:

     

    LastWeekDay = IF(WEEKDAY([Date])=6,[Date]-3,[Date]-1)
    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago
      date table?

      I have two columns named "sales" and "ship_date". I put sales in a box that displays a total of all sales and now I want to narrow it down to "last business day" sales. Do I pull over "ship_date" as a filter or a measure?