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,
Solved! Go to Solution.
Heres a view of what the original data looks:
And this is where I would like to get:
If there is no data for the previous month, the value can be null.
Try this calculated column formula
Hope this helps.
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.
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.
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 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."
Thank you so much.
I have used this logic and it works.
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
May the fourth be with you, join us online!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.