Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I ve tried your approach. Im still getting this error, when I use the date column fromthe date table:
When using the date column in the table with the quantities, I'm getting this error:
Thank you for your help.
@iiomarioii
Use this measure if you have calendar Table:
Positive Calendar =
VAR _Date = MAX('Calendar'[Date])
RETURN
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy Thank you
I'm still getting 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.
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.
Hi @iiomarioii ,
Is this problem sloved?
If not, please let me know.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@iiomarioii
Could you share a sample file for me to check how you are applying the measure?
Thanks
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |