Reply
Regular Visitor
Posts: 44
Registered: ‎01-09-2017
Accepted Solution

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?

 

Thanks in advance.

 

AS


Accepted Solutions
Established Member
Posts: 127
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.

View solution in original post


All Replies
Established Member
Posts: 127
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.

Highlighted
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?