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,
I am new to DAX language. I am using powerpivot in Excel 2016.
I have the following spreadsheet that uses DAX time function.
Date Futures Settlement Daily Adjustment
1/1/2017 10.22
1/2/2017 10.46 -0.24
1/3/2017 10.87 -0.41
1/4/2017 11.02 -0.15
1/5/2017 10.67 0.35
1/6/2017 0 0
1/7/2017 0 0
1/8/2017 10.42 0.25
1/9/2017 10.21 0.21
1/10/2017 10.1 0.11
This was done in plain excel just as an example of what I have in real life. What I need to do using powerpivot/DAX is to create the difference (Daily Adjustment column) but “skipping” the zeroes. See above that the 1/8/2017 it is the difference of 10.42 minus previous cell that has a value, in this case 10.67.
I have seen lots of posts referring to row differences by using INDEX. This is not the case here. I would like to do this by using the column [DATE]. Is there a way to do this? Like, “get current value and subtract from last date where there is a value on column “Futures Settlement”.
Thank you,
Marcos
Solved! Go to Solution.
Hi,
Enter this calculated column formula
=if(ISBLANK(CALCULATE(LASTNONBLANK(Data[Date],1),FILTER(Data,Data[Date]<EARLIER(Data[Date])),Data[Futures settlement]>0)),BLANK(),if(Data[Futures settlement]=0,0,LOOKUPVALUE(Data[Futures settlement],Data[Date],CALCULATE(LASTNONBLANK(Data[Date],1),FILTER(Data,Data[Date]<EARLIER(Data[Date])),Data[Futures settlement]>0))-Data[Futures settlement]))
Hope this helps.
Hi,
Enter this calculated column formula
=if(ISBLANK(CALCULATE(LASTNONBLANK(Data[Date],1),FILTER(Data,Data[Date]<EARLIER(Data[Date])),Data[Futures settlement]>0)),BLANK(),if(Data[Futures settlement]=0,0,LOOKUPVALUE(Data[Futures settlement],Data[Date],CALCULATE(LASTNONBLANK(Data[Date],1),FILTER(Data,Data[Date]<EARLIER(Data[Date])),Data[Futures settlement]>0))-Data[Futures settlement]))
Hope this helps.
Hello Ashish,
Thank you so much for your help. This is exactly what I was looking for. By reading your solution/formula I am starting to understand a bit more about the logic behind dax language. Fantastic.
Cheers!
Marcos
You are welcome.
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 |
---|---|
110 | |
102 | |
86 | |
77 | |
68 |
User | Count |
---|---|
120 | |
108 | |
98 | |
83 | |
77 |