Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sales for Last week same day

Hi Folks,

 

I would like to have your assistance to find Sum of Sales for Last week Same day selected date or even when there is no date selected it should show Sales for max day -7 
i.e if Today is Thursday 27.08.2020 I need to see the Sales for Last week thursday 20.08.2020

 

Right now I am using below written Dax formula

 

Same day Last week = CALCULATE(SUM(vtransaction[TXN_AMT]),DAY(vtransaction[TXN_DATE]) -7)
 
Thank you in advance

5 Replies

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

    Anonymous - I think that should be:

     

    Same day Last week = CALCULATE(SUM(vtransaction[TXN_AMT]),[TXN_DATE]=([TXN_DATE] -7))

     

    Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2. 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , you need to use date table for that.

    example

    7 Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,Day))

7th Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-7))
Last Day = CALCULATE(sum('order'[Qty]), previousday(dateadd('Date'[Date],-6,Day)))

     

    refer Power BI — Day Intelligence
    https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak 

       

      I went through your blog post and below DAX worked well for me:

       

      Same Week Day Last Week = CALCULATE([Sales], dateadd('Date'[Date],-7,Day))

       

      Thanks for your help

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak ,

       

      I used the formula from your blog and it worked fine untill i had dates in my matrix.
      I am using the below dax:

      M.Sameday_LW Sales = CALCULATE(SUM(vTransaction[EX_GST_AMT]),
      DATEADD('Dim Date'[CALENDAR_DATE_FMTD],-7,DAY))
      Which is working fine is used in the below format(i.e. going date by date):

      Location       Department        CALENDAR_DATE_FMTD    Current Date       M.Sameday_LW Sales
      101       12         30/09/2019 0:00    10           1
      101       12        1/10/2019 0:00    12           2
      101       12        2/10/2019 0:00    13           3
      101       12        3/10/2019 0:00       14          4
      101       12        4/10/2019 0:00    15          5
      101       12        5/10/2019 0:00    16          6 
      101       12        6/10/2019 0:00    17          7
      101       12        7/10/2019 0:00    18         10
      101       12        8/10/2019 0:00    19         12
      101       12        9/10/2019 0:00    20         13
      101       12       10/10/2019 0:00    21         14
      101      12      11/10/2019 0:00    22         15
      101      12      12/10/2019 0:00    23         16
      101      1213/10/2019 0:00    24         17
      Total     244         125

      But Fails when used in the below format(i.e. if we eliminate the date factor) its just shows the sum of the entire date selection instead of showing same day last week value which here should be $17:
       
      Location      Department     Current Date        M.Sameday_LW Sales
      101      12     24       125
      Expected values:
       
      Location      Department      Current Date    M.Sameday_LW Sales
      101     12      24      17
       
      Thanks in Advance!
    • rashmi_panicker's avatar
      rashmi_panicker
      Frequent Visitor
      4 years ago

      Hi Amit,

       

      Your measures will give the Sales/Qty for the same day of previous week. If the sales on that day (7 days back) is blank then my report should show the Sales/Qty of the 1st date going backwards where the sales amount is non blank. Example - if the current context date is 18th Mar, I should get the Sales for 11th but if there was no sales on 11th it should give me the sales for 10th and if there was no sales for 10th then 9th & so on till I have the value. Can you please help me tweak your measure to include the above criteria.

       

      Thank you,

      Rashmi.