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.
Hello!
My table consists of transactions where every row is either a negative or positive transaction ( i.e. profit or loss) and other info related to them.
The thing i'd like to be able to calculate is date difference between a positive transaction and the first negative transaction from that row.
So first row should be 2.1.2016, 2nd a blank, 3rd 6.1.2016, 4th 6.1.2016 and so on. I've tried all kinds of dax command compinations with min, earlier, filter etc. to get a min date from neg_date for each row so that the returned date is greater than the date but i haven't been able to get it right. I also tried to use only one supplier in the table but it didnt help. Am i trying to do it the wrong way or am I just missing something here? All help is much appreciated!
Solved! Go to Solution.
Hi @mikaro,
According to your description, you should be able to follow steps below to get your expected result.
I assume you have a table called "Table1" like below.
1. Add an Index Column in Query Editor.
2. Use the formula below to create a calculate column to get the first negative date from current row for each row.
FirstNegativeDate = VAR i = Table1[Index] RETURN IF ( Table1[Value] < 0, BLANK (), CALCULATE ( MIN ( Table1[Date] ), FILTER ( ALL ( Table1 ), Table1[Index] > i && Table1[Value] < 0 ) ) )
3. Then you should be able to calculate the difference between the positive transaction and the first negative transaction from current row.
DateDifference = IF ( Table1[FirstNegativeDate] <> BLANK (), DATEDIFF ( Table1[Date], Table1[FirstNegativeDate], DAY ) )
Here is the sample pbix file for your reference.
Regards
Hi @mikaro,
According to your description, you should be able to follow steps below to get your expected result.
I assume you have a table called "Table1" like below.
1. Add an Index Column in Query Editor.
2. Use the formula below to create a calculate column to get the first negative date from current row for each row.
FirstNegativeDate = VAR i = Table1[Index] RETURN IF ( Table1[Value] < 0, BLANK (), CALCULATE ( MIN ( Table1[Date] ), FILTER ( ALL ( Table1 ), Table1[Index] > i && Table1[Value] < 0 ) ) )
3. Then you should be able to calculate the difference between the positive transaction and the first negative transaction from current row.
DateDifference = IF ( Table1[FirstNegativeDate] <> BLANK (), DATEDIFF ( Table1[Date], Table1[FirstNegativeDate], DAY ) )
Here is the sample pbix file for your reference.
Regards
Thank you @v-ljerr-msft your solution was exactly what i was trying to achieve! Didn't know that you could write functions with variables like that in Power BI
Do u have anyother column in that table like Id etc.
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 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |