Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |