DAX - measure for daily rates on matrix

[Anonymous]

I have a transaction table (Items Sold) that has daily sum for total items per day. Some days there may be a record, some days there may not.

I also have a rate table that tracks rate changes (Effective Start Date and Effective Stop Date). For this example, the rate is per Day.

I need a DAX formula that will create a measure with this logic: Daily rate, regardless of whether there is a record for that day in the Items Sold table. This will be shown on a date matrix, with the column date field coming from a "date" table.

[Greg_Deckler]

@Anonymous This is essentially Lookupvalue Range - https://community.powerbi.com/t5/Quick-Measures-Gallery/LOOKUPVALUE-Range/m-p/974201#M430

In your case:

Measure =
  VAR __Loc = MAX('Items Sold',[Location])
  VAR __Date = MAX('Items Sold',[Date])
  VAR __Table = 
    ADDCOLUMNS(
      FILTER('Rates',[Location]=__Loc),
      "Final Effective Stop Date",IF(ISBLANK([Effective Stop Date]),TODAY(),[Effective Stop Date])
    )
RETURN
  MAXX(FILTER(__Table,__Date>=[Effective Start Date] && __Date <=[Final Effective Stop Date]),[Rate])

[Anonymous]

@Greg_Deckler 

That didn't seem to do anything for me. I had to use MAXX for __Loc and __Date because it wouldn't take MAX, but it still just returns data for the dates I have records in Items Sold table. Today I want the values to display for each day, Sept 1 - Sept 14. Should I be leveraging my date table instead of the date field on the Items Sold table?

[Greg_Deckler · Accepted solution]

@Anonymous Sorry, I had my syntax incorrect:

Measure =
  VAR __Loc = MAX('Items Sold'[Location])
  VAR __Date = MAX('Calendar'[Date])
  VAR __Table = 
    ADDCOLUMNS(
      FILTER('Rates',[Location]=__Loc),
      "Final Effective Stop Date",IF(ISBLANK([Effective Stop Date]),TODAY(),[Effective Stop Date])
    )
RETURN
  MAXX(FILTER(__Table,__Date>=[Effective Start Date] && __Date <=[Final Effective Stop Date]),[Rate])

This assumes you have Calendar[Date] in a table visual and that you have selected Location in a slicer somewhere, for example.