Last month same day & last week same day (excluding weekend & holiday)

[Anonymous]

Hello, 

I have a situation where i need to show sum of product count and Amount for category for a date selected as slicer (Single select slicer). now my requirment is to also show last week same day and last month same day count and amount though we do not have data for weekend and holidays. In such case, instead of showing 0 as amount and count, last week and last month same date should refer to max date possible excluding weekend and holiday.  here is the example - if in slicer i have 20th october selected, last month same date should be 17th september  amount and count (considering 18th as holiday and 19th and 20th as weekend). same logic should be applied in case of last week same day. Here is the sample data.

Date	Category	Product Count	Amount
03-08-2020	C	11	1624
03-08-2020	D	19	1371
12-08-2020	E	18	1354
12-08-2020	A	10	1936
12-08-2020	E	14	1323
18-08-2020	A	13	1898
18-08-2020	B	14	1954
18-08-2020	B	19	1365
18-08-2020	A	16	1554
18-08-2020	A	18	1125
18-08-2020	E	17	1188
18-08-2020	D	18	1505
18-08-2020	C	18	1226
20-08-2020	D	12	1927
21-08-2020	B	18	1403
24-08-2020	B	17	1202
25-08-2020	D	10	1505
26-08-2020	C	10	1964
27-08-2020	D	14	1590
28-08-2020	A	18	1195
31-08-2020	A	12	1694
01-09-2020	E	12	1453
02-09-2020	A	12	1326
03-09-2020	E	12	1945
04-09-2020	D	14	1343
07-09-2020	A	20	1619
08-09-2020	A	10	1872
09-09-2020	C	17	1268
10-09-2020	A	19	1266
11-09-2020	B	20	1513
14-09-2020	B	16	1087
15-09-2020	D	16	1498
16-09-2020	A	11	1972
17-09-2020	E	20	1399
21-09-2020	E	16	1797
22-09-2020	D	16	1272
23-09-2020	A	13	1944
24-09-2020	A	13	1412
25-09-2020	E	16	1959
28-09-2020	B	17	1880
29-09-2020	B	13	1127
30-09-2020	D	11	1572
01-10-2020	C	16	1802
02-10-2020	A	17	1840
05-10-2020	D	12	1361
06-10-2020	C	11	1665
07-10-2020	D	13	1727
08-10-2020	A	16	1445
09-10-2020	A	10	1687
12-10-2020	D	19	1680
13-10-2020	C	10	1902
14-10-2020	D	15	1875
15-10-2020	C	12	1786
16-10-2020	A	18	1873
19-10-2020	D	10	1944
20-10-2020	C	20	1582

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your requirement, you can follow my steps:

1. Create a date table:

Date = CALENDARAUTO()

2. Create some calculated columns for your main table:

Last month workday =

var _lastmonth=DATEADD('Date'[Date],-1,MONTH)

var _workday=

SWITCH(WEEKDAY(_lastmonth,2),

6,DATEADD(_lastmonth,-1,DAY),

7,DATEADD('Date'[Date],-2,DAY)

,_lastmonth

)

return _workday

Last week workday = DATEADD('Date'[Date],-7,DAY)

Last month workday sum amount =

CALCULATE(

    SUM('Table'[Amount]),

FILTER('Table','Table'[Date]=EARLIER('Table'[Last month workday])))

Last month workday sum count =

CALCULATE(

    SUM('Table'[Product Count]),

FILTER('Table','Table'[Date]=EARLIER('Table'[Last month workday])))

Last week workday sum amount =

CALCULATE(

    SUM('Table'[Amount]),

FILTER('Table','Table'[Date]=EARLIER('Table'[Last week workday])))

Last week workday sum count =

CALCULATE(

    SUM('Table'[Product Count]),

FILTER('Table','Table'[Date]=EARLIER('Table'[Last week workday])))

And you can get what you want, like this:

You can download my sample pbix from here

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , With help from date table

Same weekday day lasy week =Day behind Sales = CALCULATE(SUM(Table[Amount]),dateadd('Date'[Date],-7,Day))

Have these in your date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)

Work day of month = Sumx(filter(Date, [Month year] = earlier([Month Day])),[Work Day])
Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)

Measures (examples)

Month Work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && 'Date'[Work day of month] <= Max('Date'[Work day of month]) ))
last Month Work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && 'Date'[Work day of month] <= Max('Date'[Work day of month]) ))
Week till work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Work day of week] <= Max('Date'[Work day of week])))
last Week till work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[Work day of week] <= Max('Date'[Work day of week])))

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 :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.