Desktop

Highlighted
Regular Visitor
Posts: 44
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
Member
Posts: 100
Registered: ‎11-01-2017

Re: WORKING WITH DATES

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
Member
Posts: 100
Registered: ‎11-01-2017

Re: WORKING WITH DATES

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.

Regular Visitor
Posts: 44
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?