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.
Hello
I want to create a calculated column in my report.
I want that on any given row I should get the sum of sales from previous period. There are two conditions:
job id must match. the periods are not sequential so the row may contain data for november for the given id and the previous record for that job is in june
I am unable to sort the months with Job id.
Job id Month Relative Month(from Jan2018) Sales Calculated Column
1 November 2017 -2 4k 8k
1 June 2017 -6 6k
1 August 2017 -4 8k 6k
This helper column can help me to lookup the sales for that period and then I can compute how much the sales have increased or decreased since the last period
My issues: periods are not sequential....there are gaps (not necessarily selcted month-1)
I need to make sure ids match
I am unable to sort my months
Regards
Swati
Solved! Go to Solution.
Hi,
Try this
=LOOKUPVALUE(Data[Sales],Data[Month],CALCULATE(MAX(Data[Month]),FILTER(Data,Data[Job ID]=EARLIER(Data[Job ID])&&Data[Month]<EARLIER(Data[Month]))),Data[Job ID],Data[Job ID])
Does this work?
Hi,
Here is the calculated column formula i have used
=LOOKUPVALUE(Data[Sales],Data[Month],CALCULATE(MAX(Data[Month]),FILTER(Data,Data[Job ID]=EARLIER(Data[Job ID])&&Data[Month]<EARLIER(Data[Month]))))
Ashish
Thanks...I think the code breaks because for other job ids, the dates repeat.
For instance, say there are more records
ID Month
2 01_nov-2017
2 01-Aug-2017
Hi,
Try this
=LOOKUPVALUE(Data[Sales],Data[Month],CALCULATE(MAX(Data[Month]),FILTER(Data,Data[Job ID]=EARLIER(Data[Job ID])&&Data[Month]<EARLIER(Data[Month]))),Data[Job ID],Data[Job ID])
Does this work?
LIFE SAVER, spent 3 hours trying to work this out
You are welcome.
Thanks it works well
You are welcome.
Is your Month column a date time column, or is it Text?
Hello
I would like to use my relative month column as the identifier...I can bring in the month as date but currently the month is identified by the relative value
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |