## Desktop

Member
Posts: 47
Registered: ‎01-09-2017

# WORKING WITH DATES

Hi Everybody,

I have a challenge for you guys. In my dataset I have a column with dates.

Then, in my report I need to create a kpi about last 3 months sales (excluding current month). I was thinking to create a new column/measure (feel free to sugest which one suits better this matter) where I do a formula like

IF(AND(MONTH(Date)=MONTH(TODAY())-1;YEAR(Date)=YEAR(TODAY());Sales;0))

Then doing the same for MONTH(TODAY())-2 and -3, and then summing the all three columns values. However Power BI Returns me some errors.

Any suggestions?

AS

Accepted Solutions
New Contributor
Posts: 522
Registered: ‎11-01-2017

## Re: WORKING WITH DATES

[ Edited ]

Hi

There could be other smart approaches too, but just to answer your question,

1. Create a new column with this formula :

last_Month=IF( AND(MONTH(Sheet1[Date])=MONTH(TODAY())-1,YEAR(Sheet1[Date])=YEAR(TODAY())),Sheet1[Sales],0)

2. Create a new measure with this formula:

Sales_Lastmonth= SUM( Sheet1[last_Month]

This will fetch you last month sales. You can do similarly for other months too.

All Replies
New Contributor
Posts: 522
Registered: ‎11-01-2017

## Re: WORKING WITH DATES

[ Edited ]

Hi

There could be other smart approaches too, but just to answer your question,

1. Create a new column with this formula :

last_Month=IF( AND(MONTH(Sheet1[Date])=MONTH(TODAY())-1,YEAR(Sheet1[Date])=YEAR(TODAY())),Sheet1[Sales],0)

2. Create a new measure with this formula:

Sales_Lastmonth= SUM( Sheet1[last_Month]

This will fetch you last month sales. You can do similarly for other months too.

Highlighted
Member
Posts: 47
Registered: ‎01-09-2017

## Re: WORKING WITH DATES

That works perfeclty! However i'm curious about the other smart ways you can get this value. Any ideas?