cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
idontexist
Helper I
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 growthDateTDP growth conditional
91400001.01.20191
93200001.02.20191
97700001.03.20191
98400001.04.20191
97700001.05.20190
97500001.06.20190
97800001.07.20191
104800001.08.20191
107600001.09.20191
108600001.10.20191
113700001.11.20191
118000001.12.20191

 

Thank you!

1 ACCEPTED SOLUTION
idontexist
Helper I
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:

result4.png

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. 

 

result3.png

 

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)

View solution in original post

7 REPLIES 7
idontexist
Helper I
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:

result4.png

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. 

 

result3.png

 

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.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...

 

 

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. 

 

OwnerAttributeTDP growthDate
Customer Service2019/06 2019/06
Customer Service2019/07 2019/07
Customer Service2019/08 2019/08
Customer Service2019/09 2019/09
Customer Service2019/10 2019/10
Customer Service2019/11 2019/11
Customer ServiceTarget  
Finance2019/01 2019/01
Finance2019/02 2019/02
Finance2019/03 2019/03
Finance2019/04 2019/04
Finance2019/05 2019/05
Finance2019/06 2019/06
Finance2019/07 2019/07
Finance2019/08 2019/08
Finance2019/09 2019/09
Finance2019/10 2019/10
Finance2019/11 2019/11
FinancePY  
FinanceTarget  
FinanceYTD  
HR2019/01 2019/01
HR2019/02 2019/02
HR2019/03 2019/03
HR2019/04 2019/04
HR2019/05 2019/05
HR2019/06 2019/06
HR2019/07 2019/07
HR2019/08 2019/08
HR2019/09 2019/09
HR2019/10 2019/10
HR2019/11 2019/11
HRPY  
HRTarget  
HRYTD  
LegalPY  
LegalTarget  
LegalYTD  
Marketing2019/01 2019/01
Marketing2019/02 2019/02
Marketing2019/03 2019/03
Marketing2019/04 2019/04
Marketing2019/05 2019/05
Marketing2019/06 2019/06
Marketing2019/07 2019/07
Marketing2019/08 2019/08
Marketing2019/09 2019/09
Marketing2019/10 2019/10
MarketingPY  
MarketingTarget  
MarketingYTD  
Marketing2019/01 2019/01
Marketing2019/02 2019/02
Marketing2019/03 2019/03
Marketing2019/04 2019/04
Marketing2019/05 2019/05
Marketing2019/06 2019/06
Marketing2019/07 2019/07
Marketing2019/08 2019/08
MarketingPY  
MarketingTarget  
MarketingYTD  
Marketing2019/03 2019/03
Marketing2019/06 2019/06
Sales2019/01 2019/01
Sales2019/02 2019/02
Sales2019/03 2019/03
Sales2019/04 2019/04
Sales2019/05 2019/05
Sales2019/06 2019/06
Sales2019/07 2019/07
Sales2019/08 2019/08
Sales2019/09 2019/09
Sales2019/10 2019/10
Sales2019/11 2019/11
SalesPY  
SalesTarget  
SalesYTD  
Sales2019/019140002019/01
Sales2019/029320002019/02
Sales2019/039770002019/03
Sales2019/049840002019/04
Sales2019/059770002019/05
Sales2019/069750002019/06
Sales2019/079780002019/07
Sales2019/0810480002019/08
Sales2019/0910760002019/09
Sales2019/1010860002019/10
Sales2019/1111370002019/11
Sales2019/1211800002019/12
SalesPY746000 
SalesTarget1000860 
SalesYTD975000 
Supply2019/01 2019/01
Supply2019/02 2019/02
Supply2019/03 2019/03
Supply2019/04 2019/04
Supply2019/05 2019/05
Supply2019/06 2019/06
Supply2019/07 2019/07
Supply2019/08 2019/08
Supply2019/09 2019/09
Supply2019/10 2019/10
Supply2019/11 2019/11
SupplyPY  
SupplyTarget  
SupplyYTD  
Supply2019/01 2019/01
Supply2019/02 2019/02
Supply2019/03 2019/03
Supply2019/04 2019/04
Supply2019/05 2019/05
Supply2019/06 2019/06
Supply2019/07 2019/07
Supply2019/08 2019/08
Supply2019/09 2019/09
Supply2019/10 2019/10
Supply2019/11 2019/11
Supply2019/01 2019/01
Supply2019/02 2019/02
Supply2019/03 2019/03
Supply2019/04 2019/04
Supply2019/05 2019/05
Supply2019/06 2019/06
Supply2019/07 2019/07
Supply2019/08 2019/08
Supply2019/09 2019/09
Supply2019/01 2019/01
Supply2019/02 2019/02
Supply2019/03 2019/03
Supply2019/04 2019/04
Supply2019/05 2019/05
Supply2019/06 2019/06
Supply2019/07 2019/07
Supply2019/08 2019/08
Supply2019/09 2019/09
Supply2019/10 2019/10
Supply2019/11 2019/11
SupplyPY  
SupplyTarget  
SupplyYTD  
Anonymous
Not applicable

@idontexist 

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)

Hi @Anonymous 

 

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

result2.png

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.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...

Hi @Anonymous 

 

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

result1.png

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors