Different time interval Sales in Power BI

Hello friends! Recently I started working on Power BI. I had to calculate the sales for different intervals This week, Last week, This Year sales etc. I’ve compiled all the calculations here for different intervals and I’ll keep adding more.

Data

Data is in the following format and using this I had to calculate. This week sales, Last week sales, This year sales etc.

Category	Date	Sales
A	4/13/2020	1
A	4/14/2020	2
A	4/15/2020	3
A	4/16/2020	4
A	4/17/2020	5
A	4/18/2020	6
A	4/19/2020	7
A	4/20/2020	8

The outcome can be achieve in multiple ways but I did using max date and week number approach. In this blog we’ll see the max date approach.

Max date

MaxDate = MAX(Sales[Date])

1. This Week sales

TW Sales = CALCULATE(SUM(Sales[Sales]),
DATESBETWEEN(Sales[Date],[MaxDate]-WEEKDAY([MaxDate],2)+1,[MaxDate]))

2. Last Week sales

LW Sales = CALCULATE(SUM(Sales[Sales]),
DATESBETWEEN(Sales[Date],[MaxDate]-WEEKDAY([MaxDate],2)-6,[MaxDate]-WEEKDAY([MaxDate],2)))

3. Last 4 Week sales

L4W Sales = CALCULATE(SUM(Sales[Sales]),
DATESBETWEEN(Sales[Date],[MaxDate]-WEEKDAY([MaxDate],2)-21+1,[MaxDate]))

4. This Month sales or Month to date (MTD) sales

MTD Sales = CALCULATE(SUM(Sales[Sales]),
DATESBETWEEN(Sales[Date],[MaxDate]-DAY([MaxDate])+1,[MaxDate]))

5. This Year(YTD) sales

YTD Sales = CALCULATE(SUM(Sales[Sales]),
DATESBETWEEN(Sales[Date],DATE(YEAR([MaxDate]),1,1),[MaxDate]))

6. Last Year same month sales or Last Year MTD sales

LY MTD Sales = CALCULATE(SUM(Sales[Sales]),
DATESBETWEEN(Sales[Date],
DATE(YEAR([MaxDate])-1,MONTH([MaxDate]),DAY([MaxDate]))-DAY([MaxDate])+1,
DATE(YEAR([MaxDate])-1,MONTH([MaxDate]),DAY([MaxDate]))))

7. Last Year same period sales or Last Year YTD sales

LY YTD Sales = CALCULATE(SUM(Sales[Sales]),
DATESBETWEEN(Sales[Date],DATE(YEAR([MaxDate])-1,1,1),
DATE(YEAR([MaxDate])-1,MONTH([MaxDate]),DAY([MaxDate]))))

Table with all calculated metric is shown in table below.

This was about
This week sales in Power BI,
Last week sales in Power BI,
Last 4 week sales in Power BI,
This Month (MTD) sales in Power BI,
This Year (YTD) sales in Power BI,
Last Year MTD sales in Power BI,
Last Year YTD sales in Power BI

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries