cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.