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

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

Try the below DAX for the new column:

Untitled.jpg

View solution in original post

@Anonymous,

Download the Power BI file from here.

View solution in original post

15 REPLIES 15
anandav
Skilled Sharer
Skilled Sharer

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/

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

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.

Try the below DAX for the new column:

Untitled.jpg

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

@Anonymous,

Download the Power BI file from here.

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!

@Anonymous,

Could you please mark my answer as the solution?

That will help others to know the answer.

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.