Search code examples
powerbidaxmeasure

DAX Current Unit Price by Item Based on Date Range

I have a data model with a fact table called PriceList and three related dimension tables, Date, CustomerPriceGroup, and Item. The key columns of these tables are below.

PriceList

[CustomerPriceGroupID   ItemID  StartDateID EndDateID   UnitPrice   StartingDate    EndingDate
83                  14837   20130529    20130627    96.48           5/29/2013   6/27/2013
76                  14837   20130529    20130627    97.92           5/29/2013   6/27/2013
76                  14837   20160228    20160810    113.76          2/28/2016   8/10/2016
83                  14837   20160228    20160810    112.32          2/28/2016   8/10/2016][1]


**Date (sample data but dates range from 1/1/2010 to 12/31/2025)**

[DateID         Date
20130102    1/2/2013
20130103    1/3/2013
20130104    1/4/2013
20130105    1/5/2013
20130106    1/6/2013
20130107    1/7/2013
20130108    1/8/2013
20130109    1/9/2013][2]


**CustomerPriceGroup**

[CustomerPriceGroupID   Code
83                  TL
76                  LTL
20                  PL06
9                   PL01][3]


**Item**

[ItemID No
14837   F100690
14838   F100700
14839   F100710
14840   F100720][4]

What I would like to do is have a date slicer, either set as Between or Dropdown, so whether I select a range of dates or a single date, I'm returned the latest Unit Price for a given item in a given price group.

Using the PriceList fact table above, my expectation is that if I selected with my Date[Date] slicer dates between 6/1/2013 and 6/15/2013, I could product the following table:

CustomerPriceGroupID    ItemID    UnitPrice
76                      14837     97.92

Or selecting a single dropdown date for 7/1/2016, produce:

CustomerPriceGroupID    ItemID    UnitPrice
76                      14837     113.76

I've tried a few different renditions but I'm not getting there. Any thoughts?

Unit Price = 

VAR MinDate = MIN('dim Date'[Date])
VAR MaxDate = MAX('dim Date'[Date])

VAR Result = 
CALCULATE(
    MAX('fact PriceList'[UnitPrice]),
    'fact PriceList'[StartingDate] >= MinDate && 'fact PriceList'[EndingDate] <= MaxDate
)

RETURN Result

The PBIX Sample file: https://file.io/hLRRqbLGCMmF

Solution

  • It is working in your file. You just need to decide on the logic of your range.

    // the fact range has to be full INSIDE the slicer range
'fact'[SDate] >= MinDate && 'fact'[EDate] <= MaxDate


// the fact range OVERLAPS the slicer range
'fact'[SDate] <= MaxDate && 'fact'[EDate] >= MinDate