Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
Solved! Go to 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)
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)
@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.
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 |
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.
Create new column
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |