Sales for Last week same day

[Anonymous]

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

[amitchandak · Accepted solution]

@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.

[rashmi_panicker]

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.

[Anonymous]

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	12	13/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!

[Anonymous]

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

[Greg_Deckler]

@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.