Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Excel Formula to PowerBI formula

 

Hello I would like to transform these last 4 coulmn formulas to PowerBI formulas

Maturity       ID                          OBS_DATE             Price         Calculated_Closing_Date     Contract_Date_Befor   Price_Before_Date    Contract_Date_After    Price_After_Date

 

01M30007151310/11/2019     54.7611/11/201910/22/201952.5911/20/201952.61
01M30007151310/10/2019     53.5611/10/201910/22/201952.5911/20/201952.61
01M30007151310/9/2019     52.6011/9/201910/22/201952.5911/20/201952.61
01M30007151310/8/2019     52.6211/8/201910/22/201952.5911/20/201952.61
01M30007151310/7/2019     52.7311/7/201910/22/201952.5911/20/201952.61
01M30007151310/4/2019     52.7811/4/201910/22/201952.5911/20/201952.61
01M30007151310/3/2019     52.4111/3/201910/22/201952.5911/20/201952.61
01M30007151310/2/2019     52.5811/2/201910/22/201952.5911/20/2019

52.61

 

 

Table3: 

Expiration_Date                                              Settle

10/22/201952.59
11/20/201952.61
12/19/201952.48
1/21/202052.29
2/20/202052.12
3/20/202051.92
4/21/202051.72
5/19/202051.5
6/22/202051.27
7/21/202051.04
8/20/202050.85
9/22/202050.7
10/20/202050.6
11/20/202050.52
12/21/202050.41
1/20/202150.32

 

What I have in Excel:

Contract_Date_Before =IF([@[Calculated Closing Date]]<MIN(Table3[Expiration_Date]),NA(),MAX((Table3[Expiration_Date]<[@[Calculated Closing Date]])*Table3[Expiration_Date]))	

Price_Before_Date =INDEX(Table3[Settle],MATCH([@[Contract_Date_Before]],Table3[Expiration_Date],0))	

Contract_Date_After =MIN(IF(Table3[Expiration_Date]>[@[Calculated Closing Date]],Table3[Expiration_Date]))	

Price_After_Date =INDEX(Table3[Settle],MATCH([@[Contract_Date_After]],Table3[Expiration_Date],0))

 

 

Explanation:  

I calculate a contract closing date, then from a list of existing contracts [Table3] i take the closest one before the calculated closing date and the one with the closest date after the calculated closing date, with their respective prices

 

(note that, if the calculated closing date has a perfect match, just take that, so maybe an IFFERROR(index-match, formula) can work

 

I apprecciate any help, 

wish you all the best,

Luca.

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    You could use the following DAX queries:

    Contract_Date_Before =
IF (
    'Table'[Calculated_Closing_Date] < MIN ( Table3[Expiration_Date] ),
    BLANK (),
    MAXX (
        FILTER ( Table3, Table3[Expiration_Date] < 'Table'[Calculated_Closing_Date] ),
        Table3[Expiration_Date]
    )
)
    Price_Before_Date =
LOOKUPVALUE (
    Table3[Settle],
    Table3[Expiration_Date], 'Table'[Contract_Date_Before]
)
    Contract_Date_After =
MINX (
    FILTER ( Table3, Table3[Expiration_Date] > 'Table'[Calculated_Closing_Date] ),
    Table3[Expiration_Date]
)
    Price_After_Date =
LOOKUPVALUE (
    Table3[Settle],
    Table3[Expiration_Date], 'Table'[Contract_Date_After]
)

     

1 Reply

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You could use the following DAX queries:

    Contract_Date_Before =
IF (
    'Table'[Calculated_Closing_Date] < MIN ( Table3[Expiration_Date] ),
    BLANK (),
    MAXX (
        FILTER ( Table3, Table3[Expiration_Date] < 'Table'[Calculated_Closing_Date] ),
        Table3[Expiration_Date]
    )
)
    Price_Before_Date =
LOOKUPVALUE (
    Table3[Settle],
    Table3[Expiration_Date], 'Table'[Contract_Date_Before]
)
    Contract_Date_After =
MINX (
    FILTER ( Table3, Table3[Expiration_Date] > 'Table'[Calculated_Closing_Date] ),
    Table3[Expiration_Date]
)
    Price_After_Date =
LOOKUPVALUE (
    Table3[Settle],
    Table3[Expiration_Date], 'Table'[Contract_Date_After]
)