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 moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
21 | |
21 | |
13 | |
12 |
User | Count |
---|---|
43 | |
28 | |
25 | |
23 | |
22 |