cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Help with getting value for previous period dax

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Help with getting value for previous period dax

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?

7 REPLIES 7
Phil_Seamark Super Contributor
Super Contributor

Re: Help with getting value for previous period dax

Hi @svishwanathan

 

Is your Month column a date time column, or is it Text?  

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Re: Help with getting value for previous period dax

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 

Super User
Super User

Re: Help with getting value for previous period dax

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]))))

 

Untitled.png

Re: Help with getting value for previous period dax

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

Highlighted
Super User
Super User

Re: Help with getting value for previous period dax

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?

Re: Help with getting value for previous period dax

Thanks it works well

Super User
Super User

Re: Help with getting value for previous period dax

You are welcome.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 132 members 1,635 guests
Please welcome our newest community members: