Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone!
My data is structured in Excel (displayed below). I am having translating the Excel formula in Power Bi however, and I need your help please!
In Excel I sort the data by Key and by Date. In column D I then use this formula : IF(A2=A1,C2-C1,"")
Could you please help me translate this to PowerBi language please?
Thank you
A | B | C | D |
Key | Date | Value | Excel Formula |
1 | 01/01/2019 | 500 | |
1 | 01/02/2019 | 800 | 300 |
2 | 01/01/2019 | 200 | |
2 | 01/02/2019 | 400 | 200 |
2 | 01/03/2019 | 900 | 500 |
2 | 01/04/2019 | 1,700 | 800 |
3 | 01/01/2019 | 600 | |
4 | 01/01/2019 | 800 | |
4 | 01/02/2019 | 1,000 | 200 |
5 | 01/01/2019 | 50 | |
5 | 01/02/2019 | 80 | 30 |
Hi @Anonymous
I think there are a few posts dealing with this. Create a new calculated column:
NewCol = VAR PreviousDate_ = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < EARLIER ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Key] ) ) VAR PreviousValue_ = CALCULATE ( DISTINCT ( Table1[Value] ), Table1[Date] < PreviousDate_, ALLEXCEPT ( Table1, Table1[Key] ) ) RETURN IF ( NOT ISBLANK ( PreviousValue_ ), Table1[Value] - PreviousValue_ )
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
User | Count |
---|---|
48 | |
44 | |
19 | |
14 | |
13 |
User | Count |
---|---|
107 | |
55 | |
28 | |
19 | |
14 |