how to find specific day sale in a week or month

[abc_777]

hi, 

I want to find out sales for every week Wednesday, Thursday, and Friday

or

every 13th, 14th, and 15th day of each month

as I have promotion ongoing  

thanks

[amitchandak]

@abc_777 , Create a column like day of month and week day in your table

day = day([Date])

Weekday = weekday([Date]) //Sunday =1

Then you can have filter

calculate(sum(Table[Value]), filter('Date', 'Date'[Day] =15))

calculate(sum(Table[Value]), filter('Date', 'Date'[WeekDay] =15))

Use with time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]), 'Date'[Day] =15 )

for week refer

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[abc_777]

hi, thanks for your reply

is it possible to make it more clear by sharing a pbi file

hi, 

to find out sales for every week Wednesday, Thursday, and Friday

thanka

[abc_777]

@Amit 

I have done the following measure

day = day([Date]) ----------------- [Date] is from my global date table bmcalendar

Weekday = weekday([Date]) -----------------  [Date] is from my global date table bmcalendar

15th day = Calculate('Measure Table'[Actual Quantity (Measure) (KG)], filter(BMCalendar, BMCalendar[Date] = 15))   ------------------ actual quantity is my quantity measure and BMCalendar is my global calender

15th Weekday = Calculate('Measure Table'[Actual Quantity (Measure) (KG)], filter(BMCalendar, BMCalendar[Weekday] =15))

15th MTD = CALCULATE('Measure Table'[Actual Quantity (Measure) (KG)],DATESMTD('BMCalendar'[Date]), BMCalendar[day] =15 )

I got following,

where I mistake. please suggest

[Aburar_123]

HI, Can you please share an excel screenshot of you expected result.

[abc_777]

@Aburar_123 ,

I don't have an excel file for expectations. But what we have every Wednesday, Thursday, and Friday we have promotion ongoing. and for this purpose, we need this calculation

[Aburar_123 · Accepted solution]

Hi,

Please find the below solution,

Calculated columns in 'Date Dim',

   DateNo. = 'Date Dim'[Date].[Day]

   WeekDay = FORMAT('Date Dim'[Date],"ddd")

Measures,

Sales_on_13,14,15 = if(max('Date Dim'[DateNo.]) in {13,14,15},SUM(Orders[Sales]))

Sales_on_Weekdays = if(max('Date Dim'[WeekDay]) in {"Wed","Thu","Fri"},SUM(Orders[Sales]))

[abc_777]

Hi @Aburar_123 ,

Thanks, it's perfect. great