March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
In my recent quest to create or catalog as many DAX equivalents for Excel functions here are the three TBILL functions.
Pretty simple even if, again, the documentation is incorrect. The documentation for TBILLEQ states:
TBILLEQ is calculated as TBILLEQ = (365 x rate)/(360-(rate x DSM)), where DSM is the number of days between settlement and maturity computed according to the 360 days per year basis.
Except as far as I can tell DSM isn't calculated that way since the only way I can get the answer to come out correctly is just normal date math. Eh.
TBILLPRICE =
VAR __Settlement = [Settlement]
VAR __MaxMaturity =
VAR __Date = EOMONTH(__Settlement,12)
RETURN
DATE(YEAR(__Date),MONTH(__Date),DAY(__Settlement))
VAR __Maturity = MINX( { [Maturity], __MaxMaturity }, [Value] )
VAR __Discount = [Discount]
VAR __DSM = ( __Maturity - __Settlement) * 1.
RETURN
100 * ( 1 - (__Discount * __DSM) / 360)
TBILLYIELD =
VAR __Settlement = [Settlement]
VAR __MaxMaturity =
VAR __Date = EOMONTH(__Settlement,12)
RETURN
DATE(YEAR(__Date),MONTH(__Date),DAY(__Settlement))
VAR __Maturity = MINX( { [Maturity], __MaxMaturity }, [Value] )
VAR __Pr = [TBILLPRICE]
VAR __DSM = ( __Maturity - __Settlement) * 1.
RETURN
( 100 - __Pr ) / __Pr * 360 / __DSM
TBILLEQ =
VAR __Settlement = [Settlement]
VAR __MaxMaturity =
VAR __Date = EOMONTH(__Settlement,12)
RETURN
DATE(YEAR(__Date),MONTH(__Date),DAY(__Settlement))
VAR __Maturity = MINX( { [Maturity], __MaxMaturity }, [Value] )
VAR __Discount = [TBILLYIELD]
VAR __DSM = (__Maturity - __Settlement) * 1.
RETURN
( 365 * __Discount) / ( 360 - ( __Discount * __DSM) )
eyJrIjoiMDQzMWY3YWMtY2FkYS00ZjZlLWJkNTUtMzI4OTcwOWIxN2VlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9