Forum Discussion
Date when value is negative
Hi guys,
this is my table:
Table1:
| Date | Item number | Quantity (Measure) |
| 01.01.2020 | 1 | 10 |
| 02.01.2020 | 1 | 5 |
| 03.01.2020 | 1 | 0 |
| 04.01.2020 | 1 | -5 |
| 01.01.2020 | 2 | 12 |
| 02.01.2020 | 2 | 3 |
| 03.01.2020 | 2 | -2 |
| 04.01.2020 | 2 | -5 |
Quantitiy = CALCULATE (
I have different items with decreasing values and I need a table showing me the date of the different items, when the value is positive (>0) for the last time.
So for example:
Table2:
| Item number | Date |
| 1 | 03.01.2020 |
| 2 | 02.01.2020 |
Any ideas? 🙂
Thank you!
Hi iiomarioii,
Try add filter to the formula.
positive = IF ( 'Gesamt2'[Date] = CALCULATE ( MAX ( 'Date'[Date] ), FILTER ( ALLEXCEPT ( Tablle1, 'Tablle1'[Artikelnummer] ), [total] >= 0 ) ), 'Gesamt2'[Date], BLANK () )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
iiomarioii - Seems like you want a variation on Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Thank you!
I tried to implement the formula "Loopup min" but unfortunately I am not able to do it that way because my Quantity column is a measure. It's not part of table 1
iiomarioii - Right, so what you will need to do is to use SUMMARIZE or ADDCOLUMNS or a combination of the two to recreate your table as a variable within your DAX code. You can then FILTER across that table to find the rows where your measure is greater than zero. Then you get the MINX of that. Some examples of how to do it can be found here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318
https://community.powerbi.com/t5/Quick-Measures-Gallery/TRIMMEAN/m-p/1074075#M504
https://community.powerbi.com/t5/Quick-Measures-Gallery/While-Loop/m-p/637535#M320
iiomarioii
Try this as new column:Positive = IF( [Date] = CALCULATE( MAX(Table1[Date]), ALLEXCEPT(Table1,Table1[Item number]), Table1[Quantity]>0 ), [Date], BLANK() )________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
- Positive =IF([Date] =CALCULATE(MAX('Date'[Date]),ALLEXCEPT(Table1,Table1[item number]),[Quantity]>0),[Date],BLANK())Thank you! I get this error:A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
iiomarioii
I added it as a column and it can be used as a measure as well. You can add the new column as a filter in Visual filter and set it to <>=Blank to show only those records that meet the condition.
Check the File: https://1drv.ms/u/s!AmoScH5srsIYgYIMjN8BuD99S0yuAA?e=XvlDMf________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
