Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
@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])
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?
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |