fill tax rate on calendar in dax

[eliasayyy]

i have tax rate that changes over time

Date                                                 Tax Rate

Wednesday, January 1, 2020	0.15
Wednesday, August 19, 2020	0.25
Monday, February 1, 2021	0.275
Tuesday, April 12, 2022	0.3
Monday, September 26, 2022	0.3225
Tuesday, May 2, 2023	0.35

i have calendar table from jan 1 2020 to july 11 2023

how can i make my calcultion that if its february 20 2020 for example, the rate must be 0.15 and another exmple on june 5 2023 the rate is 0.35

NOTE: I know how to do it in power query i was just wondering if there is a wawy on dax without filling dates on my tax table

[barritown · Accepted solution]

Hi @eliasayyy,

You could try this [DAX] measure:

In plain text:

Current Rate = 
VAR CurrentDate = MAX ( 'Date'[Date] )
VAR ChosenRateDate = MAXX ( FILTER (  ALL ( Rates ), [Date] <= CurrentDate ), [Date] )
RETURN LOOKUPVALUE ( Rates[Tax Rate], Rates[Date], ChosenRateDate )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian