Forum Discussion

Green_Cloud's avatar
Green_Cloud
Helper I
4 years ago
Solved

Assigning Codes based on Date Range

Altruists,

 

I need DAX to assign sales code as a new column based on the date ranges (Sales Start and End date) as follows-

 

 

Can you please share the DAX?

 

  • johnt75's avatar
    johnt75
    4 years ago

    If you know that you won't have any overlapping date ranges then you could do something like

    Sales Code =
var currentSaleDate = 'Sales Table'[Sale date]
return SELECTCOLUMNS( FILTER( 'Lookup table', 'Lookup table'[Sales Start Date] <= currentSaleDate && 'Lookup table'[Sales End Date] >= currentSalesDate), "@val", 'Lookup table'[Sales Code])

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    If you know that you won't have any overlapping date ranges then you could do something like

    Sales Code =
var currentSaleDate = 'Sales Table'[Sale date]
return SELECTCOLUMNS( FILTER( 'Lookup table', 'Lookup table'[Sales Start Date] <= currentSaleDate && 'Lookup table'[Sales End Date] >= currentSalesDate), "@val", 'Lookup table'[Sales Code])