Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

2 REPLIES 2
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.