Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to Solution.
Can you please provide some sample data and example of what the output should be?
Hi,
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.
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.
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.
@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.
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?
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:
@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."
Hi,
Thank you so much.
I have used this logic and it works.
@Anonymous,
Could you please mark my answer as the solution?
That will help others to know the answer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |