cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
anandav
Continued Contributor
Continued Contributor

Try the below DAX for the new column:

Untitled.jpg

View solution in original post

anandav
Continued Contributor
Continued Contributor

@Anonymous,

Download the Power BI file from here.

View solution in original post

15 REPLIES 15
anandav
Continued Contributor
Continued Contributor

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

Anonymous
Not applicable

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.

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/
Anonymous
Not applicable

Hi @Ashish_Mathur,

 

I get the following error when trying that formula: "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

As you can see from the image that i posted, it is working absolutely fine.  Retry.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
anandav
Continued Contributor
Continued Contributor

@Anonymous,

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.

Anonymous
Not applicable

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
Continued Contributor
Continued Contributor

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

Anonymous
Not applicable

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

anandav
Continued Contributor
Continued Contributor

Try the below DAX for the new column:

Untitled.jpg

View solution in original post

Anonymous
Not applicable

@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."

anandav
Continued Contributor
Continued Contributor

@Anonymous,

Download the Power BI file from here.

View solution in original post

Capture.PNG

Hi,

Thank you so much.

I have used this logic and it works.

PrevMonActual =
CALCULATE(MIN(KPI_Summary[KPIActual]),
          FILTER(ALL(KPI_Summary),
              KPI_Summary[FiscalPeriod1] = EARLIER(KPI_Summary[previousFiscalPeriod]) &&
            KPI_Summary[KPI_DEFINITION_ID] = EARLIER(KPI_Summary[KPI_DEFINITION_ID])
              ))
 
The only problem is some values are missing , I have attached a screenshot.
Thank You.
 
 
Anonymous
Not applicable

Hi @anandav,

 

Thank you very much, this seems to work!

anandav
Continued Contributor
Continued Contributor

@Anonymous,

Could you please mark my answer as the solution?

That will help others to know the answer.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.