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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors