cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kosti_h Frequent Visitor
Frequent Visitor

Lookup for the value of previous month in the same table

HI,

 

I have been struggling now for a while with creating a new column. I have the following columns in my table:

 

Company |  Sales  |  Date

 

I created a new column to the query that gives also the date exactly one month before, named PreviousMonthDate. All the sales of the month are dated on the first day of the month. I would like to create a new column, that would show the Sales from the previous month. I tried the following DAX formula:

 

PreviousMonthSales = LOOKUPVALUE(Table[Sales]; Table[Company]; Table[Company]; Table[Date]; Table[PreviousMonthDate])

 

I get an error: "A table of multiple values was supplied where a single value was expected." 

 

How should do this? The table is structured so that there is one row for each company each month,

2 ACCEPTED SOLUTIONS

Accepted Solutions
anandav Established Member
Established Member

Re: Lookup for the value of previous month in the same table

Try the below DAX for the new column:

Untitled.jpg

View solution in original post

anandav Established Member
Established Member

Re: Lookup for the value of previous month in the same table

@kosti_h,

Download the Power BI file from here.

View solution in original post

14 REPLIES 14
anandav Established Member
Established Member

Re: Lookup for the value of previous month in the same table

Can you please provide some sample data and example of what the output should be?

kosti_h Frequent Visitor
Frequent Visitor

Re: Lookup for the value of previous month in the same table

Hi,

 

Heres a view of what the original data looks:

Capture.PNG

 

And this is where I would like to get:

 

Capture1.PNG

 

If there is no data for the previous month, the value can be null.

anandav Established Member
Established Member

Re: Lookup for the value of previous month in the same table

@kosti_h,

Write a measure as below:

Prev Month Sales = CALCULATE(MAX(Sales[Sales]), DATEADD(Sales[Date], -1, MONTH))

 

Make sure the dates are of data type date in your table.

kosti_h Frequent Visitor
Frequent Visitor

Re: Lookup for the value of previous month in the same table

Hi,

 

This one didn't solve the problem, since I can't use measure as a solution. In  the next phase I need to compare on a row level the sales of current and previous month, because I'm going to calculate new sales (previous month sales = 0), expanded sales (previous month < current month, previous month is not 0) and churn (current month sales < previous month sales). Those will be calculated on conditional columns and they can then be used in calculating metrics.

anandav Established Member
Established Member

Re: Lookup for the value of previous month in the same table

Why not write the same formula as in the measure as a new column?

kosti_h Frequent Visitor
Frequent Visitor

Re: Lookup for the value of previous month in the same table

I tried to use the same formula as a new column but it gives me only blanks.

anandav Established Member
Established Member

Re: Lookup for the value of previous month in the same table

Try the below DAX for the new column:

Untitled.jpg

View solution in original post

Super User
Super User

Re: Lookup for the value of previous month in the same table

Hi,

 

Try this calculated column formula

 

=LOOKUPVALUE(Data[Sales],[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Company]=EARLIER(Data[Company])&&Data[Date]<EARLIER(Data[Date]))),[Company],[Company])

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
kosti_h Frequent Visitor
Frequent Visitor

Re: Lookup for the value of previous month in the same table

@anandav This gives me an error: "A single value for column 'Amount' in table 'Sheet1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 322 members 2,903 guests
Please welcome our newest community members: