Forum Discussion
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
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.
2 Replies
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.