Count of sales in last three month and last three days in same table.

[shahaabz]

I am new to power bi . My question is that is it possible to count sale in last three month and last year in the same table view.

Or how many sales has been done in last month and in last last week in the same table . How to display that in one table .

Section	Count This week	Count This Month	Count This Year
Toy	500	15000	50000

[v-shex-msft · Accepted solution]

Hi @shahaabz,

You can refer to below measures:

Count of Last Week = 
var currProduct= LASTNONBLANK(Sheet1[Product Name],Sheet1[Product Name])
return
COUNTAX(FILTER(ALL(Sheet1),Sheet1[Product Name]=currProduct&&WEEKNUM(Sheet1[Date])=WEEKNUM(TODAY())-1),Sheet1[Product Name])

Count of Last Month = 
var currProduct= LASTNONBLANK(Sheet1[Product Name],Sheet1[Product Name])
return
COUNTAX(FILTER(ALL(Sheet1),Sheet1[Product Name]=currProduct&&and(MONTH(Sheet1[Date])=MONTH(EOMONTH(TODAY(),-1)),YEAR(Sheet1[Date])=YEAR(EOMONTH(TODAY(),-1)))),Sheet1[Product Name])

Count of Last Year = 
var currProduct= LASTNONBLANK(Sheet1[Product Name],Sheet1[Product Name])
return
COUNTAX(FILTER(ALL(Sheet1),Sheet1[Product Name]=currProduct&&YEAR(Sheet1[Date])=YEAR(TODAY())-1),Sheet1[Product Name])

Sample:

Data table.

 

Create a visual to display these result:

 

In addition, if you want to get the sum of amount, you can use sumx function:

Sum of Last Year = 
var currProduct= LASTNONBLANK(Sheet1[Product Name],Sheet1[Product Name])
return
SUMX(FILTER(ALL(Sheet1),Sheet1[Product Name]=currProduct&&YEAR(Sheet1[Date])=YEAR(TODAY())-1),Sheet1[Amount])

Regards,
Xiaoxin Sheng

[ankitpatira]

@shahaabz You can create three seperate measures under Modelling tab to achieve that for the same table.

[shahaabz]

Thanx @ankitpatira but as i am new to power bi I dont know how to that.