Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

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.

Helper I

## how to check if current value more or less than previous power bi

Hi Power BI dax Gurus!

Got stuck with following thing - i need to check if previous value more or less than current. Example is below.

I have column "TDP growth" as value and column Date. Column "TDP growth conditional" should check if  current value more or less than previous.

 TDP growth Date TDP growth conditional 914000 01.01.2019 1 932000 01.02.2019 1 977000 01.03.2019 1 984000 01.04.2019 1 977000 01.05.2019 0 975000 01.06.2019 0 978000 01.07.2019 1 1048000 01.08.2019 1 1076000 01.09.2019 1 1086000 01.10.2019 1 1137000 01.11.2019 1 1180000 01.12.2019 1

Thank you!

1 ACCEPTED SOLUTION
Helper I

Guys,

You gave me an idea and i solved my request. Idea have following steps:

1. Create a measure that took previous value from same column.

a. Here i need to create index column based on TDP growth

b. Use a formula LOOKUPVALUE('KPI List v2'[TDP growth];'KPI List v2'[Index];'KPI List v2'[Index]-1)+0

Now i can get this:

2. Create new column that check if current value > than previous, using this formula: IF('KPI List v2'[TDP growth]>Column_new;1;0)

And all of this perfectly fit my demand.

Final formula (aggregated from two), looks like the following:

TDP growth conditional final =
var Column_new = LOOKUPVALUE('KPI List v2'[TDP growth];'KPI List v2'[Index];'KPI List v2'[Index]-1)+0
return
IF('KPI List v2'[TDP growth]>Column_new;1;0)

7 REPLIES 7
Helper I

Guys,

You gave me an idea and i solved my request. Idea have following steps:

1. Create a measure that took previous value from same column.

a. Here i need to create index column based on TDP growth

b. Use a formula LOOKUPVALUE('KPI List v2'[TDP growth];'KPI List v2'[Index];'KPI List v2'[Index]-1)+0

Now i can get this:

2. Create new column that check if current value > than previous, using this formula: IF('KPI List v2'[TDP growth]>Column_new;1;0)

And all of this perfectly fit my demand.

Final formula (aggregated from two), looks like the following:

TDP growth conditional final =
var Column_new = LOOKUPVALUE('KPI List v2'[TDP growth];'KPI List v2'[Index];'KPI List v2'[Index]-1)+0
return
IF('KPI List v2'[TDP growth]>Column_new;1;0)

Anonymous
Not applicable

@idontexist   i think there is something summerezied in your table visual. because i have checked with the same dataset which you have given and it is exactly matching with your expected result.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Helper I

Hi @Anonymous

I found the problem why is that. Basically dataset in cube contains a lot more rows which are blank. This was not expected by me in initial request.

Attaching full extract.

 Owner Attribute TDP growth Date Customer Service 2019/06 2019/06 Customer Service 2019/07 2019/07 Customer Service 2019/08 2019/08 Customer Service 2019/09 2019/09 Customer Service 2019/10 2019/10 Customer Service 2019/11 2019/11 Customer Service Target Finance 2019/01 2019/01 Finance 2019/02 2019/02 Finance 2019/03 2019/03 Finance 2019/04 2019/04 Finance 2019/05 2019/05 Finance 2019/06 2019/06 Finance 2019/07 2019/07 Finance 2019/08 2019/08 Finance 2019/09 2019/09 Finance 2019/10 2019/10 Finance 2019/11 2019/11 Finance PY Finance Target Finance YTD HR 2019/01 2019/01 HR 2019/02 2019/02 HR 2019/03 2019/03 HR 2019/04 2019/04 HR 2019/05 2019/05 HR 2019/06 2019/06 HR 2019/07 2019/07 HR 2019/08 2019/08 HR 2019/09 2019/09 HR 2019/10 2019/10 HR 2019/11 2019/11 HR PY HR Target HR YTD Legal PY Legal Target Legal YTD Marketing 2019/01 2019/01 Marketing 2019/02 2019/02 Marketing 2019/03 2019/03 Marketing 2019/04 2019/04 Marketing 2019/05 2019/05 Marketing 2019/06 2019/06 Marketing 2019/07 2019/07 Marketing 2019/08 2019/08 Marketing 2019/09 2019/09 Marketing 2019/10 2019/10 Marketing PY Marketing Target Marketing YTD Marketing 2019/01 2019/01 Marketing 2019/02 2019/02 Marketing 2019/03 2019/03 Marketing 2019/04 2019/04 Marketing 2019/05 2019/05 Marketing 2019/06 2019/06 Marketing 2019/07 2019/07 Marketing 2019/08 2019/08 Marketing PY Marketing Target Marketing YTD Marketing 2019/03 2019/03 Marketing 2019/06 2019/06 Sales 2019/01 2019/01 Sales 2019/02 2019/02 Sales 2019/03 2019/03 Sales 2019/04 2019/04 Sales 2019/05 2019/05 Sales 2019/06 2019/06 Sales 2019/07 2019/07 Sales 2019/08 2019/08 Sales 2019/09 2019/09 Sales 2019/10 2019/10 Sales 2019/11 2019/11 Sales PY Sales Target Sales YTD Sales 2019/01 914000 2019/01 Sales 2019/02 932000 2019/02 Sales 2019/03 977000 2019/03 Sales 2019/04 984000 2019/04 Sales 2019/05 977000 2019/05 Sales 2019/06 975000 2019/06 Sales 2019/07 978000 2019/07 Sales 2019/08 1048000 2019/08 Sales 2019/09 1076000 2019/09 Sales 2019/10 1086000 2019/10 Sales 2019/11 1137000 2019/11 Sales 2019/12 1180000 2019/12 Sales PY 746000 Sales Target 1000860 Sales YTD 975000 Supply 2019/01 2019/01 Supply 2019/02 2019/02 Supply 2019/03 2019/03 Supply 2019/04 2019/04 Supply 2019/05 2019/05 Supply 2019/06 2019/06 Supply 2019/07 2019/07 Supply 2019/08 2019/08 Supply 2019/09 2019/09 Supply 2019/10 2019/10 Supply 2019/11 2019/11 Supply PY Supply Target Supply YTD Supply 2019/01 2019/01 Supply 2019/02 2019/02 Supply 2019/03 2019/03 Supply 2019/04 2019/04 Supply 2019/05 2019/05 Supply 2019/06 2019/06 Supply 2019/07 2019/07 Supply 2019/08 2019/08 Supply 2019/09 2019/09 Supply 2019/10 2019/10 Supply 2019/11 2019/11 Supply 2019/01 2019/01 Supply 2019/02 2019/02 Supply 2019/03 2019/03 Supply 2019/04 2019/04 Supply 2019/05 2019/05 Supply 2019/06 2019/06 Supply 2019/07 2019/07 Supply 2019/08 2019/08 Supply 2019/09 2019/09 Supply 2019/01 2019/01 Supply 2019/02 2019/02 Supply 2019/03 2019/03 Supply 2019/04 2019/04 Supply 2019/05 2019/05 Supply 2019/06 2019/06 Supply 2019/07 2019/07 Supply 2019/08 2019/08 Supply 2019/09 2019/09 Supply 2019/10 2019/10 Supply 2019/11 2019/11 Supply PY Supply Target Supply YTD
Anonymous
Not applicable
``````Measure =
VAR _prevdate = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])))
VAR _prevValue =  CALCULATE(SUM('Table'[TDP growth]),'Table'[Date]=_prevdate)
RETURN IF(MAX('Table'[TDP growth])>_prevValue,1,0)``````
Helper I

Hi @Anonymous

Thanks for your help. Unfortunately, the same wrong result.

Anonymous
Not applicable

Create new column

Flag =
VAr Column_new = CALCULATE(MAX(Sheet1[TDP growth]),FILTER(Sheet1,Sheet1[Date]<EARLIER(Sheet1[Date])))
return
IF(Sheet1[TDP growth]>Column_new,1,0)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Helper I

Hi @Anonymous

Thanks for help. Unfortunately it gives a bit wrong result.

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors