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

[idontexist]

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!

[idontexist · Accepted solution]

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]

@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...

[idontexist]

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]

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

[idontexist]

Hi @Anonymous 

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

[Anonymous]

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...

[idontexist]

Hi @Anonymous 

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